Clear() public method

Clears the table of all data.
public Clear ( ) : void
return void
Beispiel #1
1
        public void DajPonudu(DataTable ponuda, Int32 ponuda_ID)
        {
            SqlConnection _konekcijaSqlConnection = new SqlConnection();
            using (_konekcijaSqlConnection = Konekcija.DajKonekciju())
            {
                SqlDataAdapter _dajPonuduSQLDataAdapter = new SqlDataAdapter();
                SqlCommand _dajPoslednjuPonuduSqlCommand = new SqlCommand("SELECT  * FROM  vwPonudaZaglavlje where Ponuda_ID = " + ponuda_ID, _konekcijaSqlConnection);

                _dajPonuduSQLDataAdapter.SelectCommand = _dajPoslednjuPonuduSqlCommand;

                //isprazni tabelu
                ponuda.Clear();

                try
                {
                    //napuni tabelu 
                    _dajPonuduSQLDataAdapter.Fill(ponuda);
                }
                catch (Exception)
                {
                    throw;
                }
            }

        }
Beispiel #2
0
        /// <summary>
        /// Получение OTS по API в будний день
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void GetResponce_Click(object sender, EventArgs e)
        {
            //очистим скверну
            {
                dgvExcel.DataSource = null;
                dgvExcel.Columns.Clear();
                dgvExcel.Rows.Clear();
                _table?.Clear();
            }
            Root root     = new Root();
            Root responce = root.FromJson(dDateLoadOTS.Value, (int)DaysofWeek.Будние);

            DateTime[]          date     = new DateTime[24];
            Properties.Settings settings = new Properties.Settings();
            for (int i = dDateLoadOTS.Value.Date.Hour; i < 24; i++)
            {
                date[i] = dDateLoadOTS.Value.Date.AddHours(i);
            }
            DataRow row;

            for (int i = 0; i < 24; i++)
            {
                row    = AddInformation().NewRow();
                row[0] = tbUID?.Text;
                row[1] = responce.Data.DataData[0].Values[i];
                row[2] = date[i];
                AddInformation().Rows.Add(row);
            }
            dgvExcel.DataSource = AddInformation();
        }
Beispiel #3
0
        public void LoadData()
        {
            //string mid = Session["Mid"].ToString();
            string mid = "930251337";
            SqlDataAdapter da = new SqlDataAdapter("", connection);
            DataTable dt = new DataTable();
            da.SelectCommand.CommandText = "SELECT * FROM MemberList WHERE Mid = @mid";
            da.SelectCommand.Parameters.AddWithValue("@mid", mid);
            da.Fill(dt);
            if (dt.Rows[0]["ImageExtention"].ToString().Length != 0)
            {
                imgProfile.ImageUrl = dt.Rows[0]["Image"].ToString() + dt.Rows[0]["ImageExtention"].ToString();
            }
            lblFullNameUP.Text = dt.Rows[0]["FullName"].ToString();
            lblMembershipStatusUP.Text = dt.Rows[0]["Status_ID"].ToString();
            lblFieldUP.Text = dt.Rows[0]["Title"].ToString();
            lblBioUP.Text = dt.Rows[0]["Bio"].ToString();

            if (dt.Rows[0]["Github"].ToString().Length == 0)
                github.Visible = false;
            else
            {
                lblgithub.Text ='/' + dt.Rows[0]["Github"].ToString();
                github.HRef = "https://github.com/" + dt.Rows[0]["Github"].ToString();
            }

            if (dt.Rows[0]["Linkedin"].ToString().Length == 0)
                linkedin.Visible = false;
            else
            {
                lblLinkedin.Text ='/' + dt.Rows[0]["Linkedin"].ToString();
                //linkedin.HRef = "https://Linkedin.com/" + dt.Rows[0]["Linkedin"].ToString();
            }

            if (dt.Rows[0]["Twitter"].ToString().Length == 0)
                twitter.Visible = false;
            else
            {
                lblTwitter.Text ='/' + dt.Rows[0]["Twitter"].ToString();
                //twitter.HRef = "https://Twitter.com/" + dt.Rows[0]["Twitter"].ToString();
            }
            lblFirstName.Text = dt.Rows[0]["FirstName"].ToString();
            lblLastName.Text = dt.Rows[0]["LastName"].ToString();
            lblField.Text = dt.Rows[0]["Title"].ToString();
            lblPhone1.Text = dt.Rows[0]["PhoneNumber"].ToString();
            lblPhone2.Text = dt.Rows[0]["AltPhoneNumber"].ToString();
            lblEmail.Text = dt.Rows[0]["Email"].ToString();
            lblNationalID.Text = dt.Rows[0]["NationalID"].ToString();
            lblStudentID.Text = dt.Rows[0]["Mid"].ToString();
            da.SelectCommand.Parameters.Clear();
            dt.Clear();
            da.SelectCommand.CommandText = "SELECT * FROM MemberSkillList WHERE Member_ID = @mid2";
            da.SelectCommand.Parameters.AddWithValue("@mid2", mid);
            da.Fill(dt);
            dlSkills.DataSource = dt;
            dlSkills.DataBind();
            da.SelectCommand.Parameters.Clear();
            dt.Clear();
        }
Beispiel #4
0
        public static void GetQuestions(string subject)
        {
            List<Q1> list = BussDAL.GetQuestions(subject);
            if (list != null && list.Count > 0)
            {
                DataTable tmp = new DataTable("mfg_t_secque2");
                tmp.Columns.Add("f_secid", typeof(int));
                tmp.Columns.Add("f_qid", typeof(int));
                tmp.Columns.Add("f_state", typeof(short));
                tmp.Columns.Add("f_subject", typeof(string));
                foreach (Q1 item in list)
                {
                    DataRow dr = tmp.NewRow();
                    dr["f_secid"] = item.f_mainsec;
                    dr["f_qid"] = item.f_id;
                    dr["f_state"] = 1;
                    dr["f_subject"] = item.f_subject;
                    tmp.Rows.Add(dr);
                    string[] array = item.f_secorder.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    if (array.Length > 1)
                    {
                        //有从知识点
                        foreach (string csec in array)
                        {
                            int _sec = int.Parse(csec);
                            if (_sec != item.f_mainsec)
                            {
                                DataRow _dr = tmp.NewRow();
                                _dr["f_secid"] = _sec;
                                _dr["f_qid"] = item.f_id;
                                _dr["f_state"] = 0;
                                _dr["f_subject"] = item.f_subject;
                                tmp.Rows.Add(_dr);
                            }
                        }
                    }
                    if (tmp.Rows.Count >= 30000)
                    {
                        int total = MySqlHelper.BulkInsert(tmp, DataBase.CResourceKF);
                        if (total > 0 && total == tmp.Rows.Count)
                        {
                            Console.WriteLine("[{0}] {1}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "1111入库");
                            tmp.Clear();
                        }
                    }
                }

                if (tmp.Rows.Count > 0)
                {
                    int total = MySqlHelper.BulkInsert(tmp, DataBase.CResourceKF);
                    if (total > 0 && total == tmp.Rows.Count)
                    {
                        Console.WriteLine("[{0}] {1}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "入库");
                        tmp.Clear();
                    }
                }
            }
        }
Beispiel #5
0
    protected void Page_Load(object sender, EventArgs e)
    {
        AdminPopedom.IsHoldModel("04");

        DataTable sdt = new DataTable();
        string sqlt = "select NameID,ComName from TGameNameInfo where Enable=1 ";
        sdt = DbSession.Default.FromSql( sqlt ).ToDataTable(); //sqlconn.sqlReader(sqlt);
        if (sdt.Rows.Count > 0)
        {
            GameLists.DataSource = sdt;
            GameLists.DataBind();
        }
        else
            this.NoGameList.Visible = true;
        sdt.Clear();

        ids = CommonManager.Web.Request( "id", "" ); //Limit.editCharacter(Limit.getFormValue("id"));
        if (ids.ToString() == "") ids = "1001";
        if (ids != "")
        {
            DataTable sd2 = new DataTable();
            string sql2 = "select ComName from TGameNameInfo where NameID=" + ids + "";
            //Response.Write(sql2 + "<br>");
            sd2 = DbSession.Default.FromSql( sql2 ).ToDataTable(); //sqlconn.sqlReader(sql2);
            if( sd2.Rows.Count > 0 )
            {
                this.ComName = sd2.Rows[0]["ComName"].ToString();

            }
            else
            {
                //Limit.outMsgBox( "温馨提示:\\n\\n 暂无任何记录!", true );
                CommonManager.Web.RegJs( this, "alert('暂无任何记录。');", false );
                return;
            }
        }
        else
        {
            CommonManager.Web.RegJs( this, "alert('参数传递错误。');", false );
            return;
        }
        sdt.Clear();
        DataTable sd = new DataTable();
        //string sqlActive = "select top 30 u.name as name,r.win as win,r.lost as lost,r.cut as cut,r.PlayTimeCount as PlayTimeCount";
        //sqlActive = sqlActive + ",r.OnLineTimeCount as OnLineTimeCount,r.point as point from " + SearchTable + " as r,Users as u where u.userid=r.userid order by point desc";
        string sqlActive = "select * from Web_VMatchTable where Nameid=" + ids + "";
        //Response.Write(sqlActive);
        sd = DbSession.Default.FromSql( sqlActive ).ToDataTable(); //sqlconn.sqlReader(sqlActive);
        //Response.End();
        if (sd.Rows.Count > 0)
        {
            ListsInfo.DataSource = sd;
            ListsInfo.DataBind();
        }
        else
            this.NoList.Visible = true;
        sd.Clear();
    }
        //Startup checker
        public static bool StartupDataBaseChecker(DBQueres DataWriter, DatabaseRead DataReader, MysqlRowCounter TableRowCounter)
        {
            try
            {
                DataTable errorCodeDataTable = new DataTable();
                errorCodeDataTable = DataReader(string.Format("SELECT * FROM {0} WHERE {1}={2} AND {3}='Profit'", thisDataSet.ThisTables[16][0], thisDataSet.ThisColumn[0][0],AccountingVariables.PETUNIA_DATE, thisDataSet.ThisColumn[1][0]));
                if (errorCodeDataTable.Rows.Count.Equals(0))
                {
                    DataWriter(string.Format("INSERT INRO {0} VALUES('{1}','Profit',0,0,0,0", thisDataSet.ThisTables[16][0], AccountingVariables.PETUNIA_DATE));
                }
                errorCodeDataTable.Clear();
                errorCodeDataTable = DataReader(string.Format("SELECT * FROM {0} WHERE {1}={2} AND {3}='Salary'", thisDataSet.ThisTables[16][0], thisDataSet.ThisColumn[0][0], AccountingVariables.PETUNIA_DATE, thisDataSet.ThisColumn[1][0]));
                if (errorCodeDataTable.Rows.Count.Equals(0))
                {
                    DataWriter(string.Format("INSERT INRO {0} VALUES('{1}','Salary',0,0,0,0", thisDataSet.ThisTables[16][0], AccountingVariables.PETUNIA_DATE));
                }
                errorCodeDataTable.Clear();
                errorCodeDataTable = DataReader(string.Format("SELECT * FROM {0} WHERE {1}={2} AND {3}='Rent'", thisDataSet.ThisTables[16][0], thisDataSet.ThisColumn[0][0], AccountingVariables.PETUNIA_DATE, thisDataSet.ThisColumn[1][0]));
                if (errorCodeDataTable.Rows.Count.Equals(0))
                {
                    DataWriter(string.Format("INSERT INRO {0} VALUES('{1}','Rent',0,0,0,0", thisDataSet.ThisTables[16][0], AccountingVariables.PETUNIA_DATE));
                }
                errorCodeDataTable.Clear();
                errorCodeDataTable = DataReader(string.Format("SELECT * FROM {0} WHERE {1}={2} AND {3}='OfficeExpance'", thisDataSet.ThisTables[16][0], thisDataSet.ThisColumn[0][0], AccountingVariables.PETUNIA_DATE, thisDataSet.ThisColumn[1][0]));
                if (errorCodeDataTable.Rows.Count.Equals(0))
                {
                    DataWriter(string.Format("INSERT INRO {0} VALUES('{1}','OfficeExpance',0,0,0,0", thisDataSet.ThisTables[16][0], AccountingVariables.PETUNIA_DATE));
                }
                errorCodeDataTable.Clear();
                errorCodeDataTable = DataReader(string.Format("SELECT * FROM {0} WHERE {1}='{2}' AND {3}='Stationary'", thisDataSet.ThisTables[16][0], thisDataSet.ThisColumn[0][0], AccountingVariables.PETUNIA_DATE, thisDataSet.ThisColumn[1][0]));
                if (errorCodeDataTable.Rows.Count.Equals(0))
                {
                    DataWriter(string.Format("INSERT INRO {0} VALUES('{1}','Stationary',0,0,0,0", thisDataSet.ThisTables[16][0], AccountingVariables.PETUNIA_DATE));
                }
                errorCodeDataTable.Clear();
                errorCodeDataTable = DataReader(string.Format("SELECT * FROM {0} WHERE {1}={2} AND {3}='Others'", thisDataSet.ThisTables[16][0], thisDataSet.ThisColumn[0][0], AccountingVariables.PETUNIA_DATE, thisDataSet.ThisColumn[1][0]));
                if (errorCodeDataTable.Rows.Count.Equals(0))
                {
                    DataWriter(string.Format("INSERT INRO {0} VALUES('{1}','Others',0,0,0,0", thisDataSet.ThisTables[16][0], AccountingVariables.PETUNIA_DATE));
                }
                errorCodeDataTable.Clear();
                errorCodeDataTable = DataReader(string.Format("SELECT * FROM {0} WHERE {1}='{2}'", thisDataSet.ThisTables[6][0], thisDataSet.ThisColumn[0][0], AccountingVariables.PETUNIA_DATE));
                if (errorCodeDataTable.Rows.Count.Equals(0))
                {
                    DataWriter(string.Format("INSERT INTO {0} VALUES ('{1}',0,0,0,0,0)", thisDataSet.ThisTables[6][0], AccountingVariables.PETUNIA_DATE));
                }

                return true;
            }
            catch (Exception)
            {

                return false;
            }
        }
    protected void BindData()
    {
        DataTable dt = new DataTable();
        string type = CommonManager.Web.Request( "type", "" );
        if( CommonManager.String.IsInteger( type ) && type.Equals( "0" ) )
        {
            //Award
            dt = member.GetGameNameForAward();
            if( dt.Rows.Count > 0 )
            {
                for( int i = 0; i < dt.Rows.Count; i++ )
                    listLeft.Items.Add( new ListItem( dt.Rows[i]["ComName"].ToString(), dt.Rows[i]["NameID"].ToString() ) );
            }
            dt.Clear();

            //
            dt = member.GetFilterGameNameForAward();
            if( dt.Rows.Count > 0 )
            {
                for( int i = 0; i < dt.Rows.Count; i++ )
                    listRight.Items.Add( new ListItem( dt.Rows[i]["ComName"].ToString(), dt.Rows[i]["NameID"].ToString() ) );
            }
            dt.Clear();
            this.map.Style.Add("display", "block");
        }

        if( CommonManager.String.IsInteger( type ) && type.Equals( "1" ) )
        {
            //extent
            dt = member.GetGameNameForExtent();
            if( dt.Rows.Count > 0 )
            {
                for( int i = 0; i < dt.Rows.Count; i++ )
                    listLeft.Items.Add( new ListItem( dt.Rows[i]["ComName"].ToString(), dt.Rows[i]["NameID"].ToString() ) );
            }
            dt.Clear();

            //
            dt = member.GetFilterGameNameForExtent();
            if( dt.Rows.Count > 0 )
            {
                for( int i = 0; i < dt.Rows.Count; i++ )
                    listRight.Items.Add( new ListItem( dt.Rows[i]["ComName"].ToString(), dt.Rows[i]["NameID"].ToString() ) );
            }
            dt.Clear();
            this.map.Style.Add("display", "block");
        }
    }
Beispiel #8
0
        public void DajSveUsluge(DataTable usluga)
        {
            SqlConnection _konekcijaSqlConnection = new SqlConnection();
            using (_konekcijaSqlConnection = Konekcija.DajKonekciju())
            {
                SqlDataAdapter _dajSveUslugeSqlDataAdapter = new SqlDataAdapter();
                SqlCommand _dajSveUslugeSqlCommand = new SqlCommand("SELECT Usluga_ID, [Stara šifra], Naziv, Cena, [Norma sati], Napomena FROM vwUsluga", _konekcijaSqlConnection);

                _dajSveUslugeSqlDataAdapter.SelectCommand = _dajSveUslugeSqlCommand;

                usluga.Clear();

                try
                {
                    _konekcijaSqlConnection.Open();

                    _dajSveUslugeSqlDataAdapter.Fill(usluga);

                    _konekcijaSqlConnection.Close();
                }
                catch (Exception)
                {
                    throw;
                }
            }

        }
Beispiel #9
0
        public DataTable BuscaDadosNF()
        {
            DataTable dt = new DataTable();
            try
            {
                sSQL = "SELECT " + this.sCampos + " FROM " + this.sTabela;
                if (!(this.sInner.Equals(String.Empty)))
                    sSQL += this.sInner.ToString();

                if (!(this.sWhere.Equals(String.Empty)))
                    sSQL += " WHERE " + this.sWhere + " ";

                if (!(this.sOrder.Equals(String.Empty)))
                    sSQL += " ORDER BY " + this.sOrder;
                FbDataAdapter Da = new FbDataAdapter(sSQL, cx.get_Conexao());
                cx.Open_Conexao();
                dt.Clear();
                Da.Fill(dt);
                Da.Dispose();
            }
            catch (Exception)
            {
                throw;
            }
            finally { cx.Close_Conexao(); }
            return dt;
        }
    protected void Page_Load(object sender, EventArgs e)
    {
        string ls_docids="";
        string ls_filepath="";

        if (this.Request.QueryString["docids"] != null)
        {
            ls_docids = this.Request.QueryString["docids"].ToString();
        }
        string[] lv_docids;
        lv_docids = ls_docids.Split(',');
        HyoaClass.Hyoa_fileatt Hyoa_fileatt = new HyoaClass.Hyoa_fileatt();
        HyoaClass.Hyoa_global Hyoa_global = new HyoaClass.Hyoa_global();
        DataTable dt = new DataTable();
        for (int j = 0; j < lv_docids.Length; j++)
        {
            dt = Hyoa_fileatt.Getfileatt(lv_docids[j]);
            if (dt.Rows[0]["hy_userid"].ToString() == Session["hyuid"].ToString() || Hyoa_global.isHaveRole("Role9995", Session["hyuid"].ToString()))
            {
                ls_filepath = Server.MapPath("~/") + dt.Rows[0]["hy_filepath"].ToString();
                //this.Response.Write(ls_filepath);
                //return;
                if (File.Exists(ls_filepath))
                {
                    System.IO.File.Delete(ls_filepath);
                }
                Hyoa_fileatt.ID = lv_docids[j];
                Hyoa_fileatt.Delete();
            }
            dt.Clear();
        }
        this.Response.Write("aaa");
        return ;
    }
Beispiel #11
0
 public int AddInfoModel(DataTable dt, string TableName)
 {
     int num;
     string str = "";
     string str2 = "";
     for (num = 0; num < dt.Rows.Count; num++)
     {
         if ((dt.Rows.Count - 1) == num)
         {
             str = str + "[" + dt.Rows[num]["FieldName"].ToString() + "])";
             str2 = str2 + "@" + dt.Rows[num]["FieldName"].ToString() + ");select scope_identity()";
         }
         else
         {
             str = str + "[" + dt.Rows[num]["FieldName"].ToString() + "],";
             str2 = str2 + "@" + dt.Rows[num]["FieldName"].ToString() + ",";
         }
     }
     StringBuilder builder = new StringBuilder();
     builder.Append("insert into " + TableName + "(");
     builder.Append(str);
     builder.Append(" values (");
     builder.Append(str2);
     SqlParameter[] commandParameters = new SqlParameter[dt.Rows.Count];
     for (num = 0; num < dt.Rows.Count; num++)
     {
         commandParameters[num] = new SqlParameter("@" + dt.Rows[num]["FieldName"].ToString(), dt.Rows[num]["FieldValue"].ToString());
     }
     int num2 = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringKy, CommandType.Text, builder.ToString(), commandParameters));
     dt.Clear();
     dt.Dispose();
     return num2;
 }
Beispiel #12
0
    public DataSet GetModelHtmlValue(string ChId, string Id)
    {
        int MyChId = int.Parse(ChId);
        ChannelModel = MyChId <= 0 ? null : ChannelBll.GetChannel(MyChId);
        MInfoModel = BInfoModel.GetModel(ChannelModel.ModelType);
        DataTable dt=new DataTable();
        DataRow dr = BInfoOper.GetInfo(MInfoModel.TableName, int.Parse(Id));
        dt = dr.Table.Copy();
        dt.Clear();
        dt.ImportRow(dr);
        dt.TableName = "DrInfo";

        DataSet ds = new DataSet();
        try
        {
            ds.Tables.Add(BModelField.GetList(ChannelModel.ModelType).Copy());
            ds.Tables.Add(dt);
        }
        catch(Exception ex)
        {
            Response.Write(ex);
        }

        return ds;
    }
Beispiel #13
0
        public void NadjiArtikal(DataTable artikal, string sQLNaredba)
        {
            SqlConnection _konekcijaSqlConnection = new SqlConnection();
            using (_konekcijaSqlConnection = Konekcija.DajKonekciju())
            {
                SqlDataAdapter _nadjiArtikalSQLDataAdapter = new SqlDataAdapter();
                SqlCommand _nadjiArtikalSQLCommand = new SqlCommand();

                _nadjiArtikalSQLCommand.CommandText = sQLNaredba;

                _nadjiArtikalSQLCommand.Connection = _konekcijaSqlConnection;

                _nadjiArtikalSQLDataAdapter.SelectCommand = _nadjiArtikalSQLCommand;

                //isprazni tabelu
                artikal.Clear();

                //napuni tabelu
                try
                {
                    _nadjiArtikalSQLDataAdapter.Fill(artikal);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        private void btnLoadImage_Click(object sender, EventArgs e)
        {
            btnLoadImage.Enabled = false;
            
            OpenFileDialog dlg = new OpenFileDialog();
            _WhitePix = 0;
            _NonWhitePix = 0;

            dlg.Title = "Open Image";
            dlg.Filter = "JPG|*.jpg;*.jpeg|PNG|*.png|"
       + "All Graphics Types|*.jpg;*.jpeg;*.png;";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                _imagePath = dlg.FileName;
                imgSource.Image = Image.FromFile(dlg.FileName);
            }
            dlg.Dispose();

            DataTable dt = new DataTable();
            dt.Clear();
            dt.Columns.Add("Image_Source");
            dt.Rows.Add(new object []{_imagePath});
            GenerateSummary(dt, false);
            btnLoadImage.Enabled = true;
        }
        private void contarSeleccionadosCustodia()
        {
            SELECCION_CUSTODIA = VALORES_CUSTODIA.Copy();
            SELECCION_CUSTODIA.Clear();
            foreach(DataGridViewRow row in tblCustodia.Rows)
            {
                if (Convert.ToBoolean(row.Cells["CHECK"].Value))
                {
                    DataRow nuevo = SELECCION_CUSTODIA.NewRow();
                    nuevo.SetField<int>("ID_PRESTAMO", VALORES_CUSTODIA.Rows[row.Index].Field<int>("ID_PRESTAMO"));
                    nuevo.SetField<int>("ID_LINEA", VALORES_CUSTODIA.Rows[row.Index].Field<int>("ID_LINEA"));
                    nuevo.SetField<string>("CONTRATO", VALORES_CUSTODIA.Rows[row.Index].Field<string>("CONTRATO"));
                    nuevo.SetField<double>("CANTIDAD", VALORES_CUSTODIA.Rows[row.Index].Field<double>("CANTIDAD"));
                    nuevo.SetField<string>("ARTICULO", VALORES_CUSTODIA.Rows[row.Index].Field<string>("ARTICULO"));
                    nuevo.SetField<string>("DESCRIPCION", VALORES_CUSTODIA.Rows[row.Index].Field<string>("DESCRIPCION"));
                    nuevo.SetField<decimal>("VALOR", VALORES_CUSTODIA.Rows[row.Index].Field<decimal>("VALOR"));

                    nuevo.SetField<string>("COD_TRANS", VALORES_CUSTODIA.Rows[row.Index].Field<string>("COD_TRANS"));
                    nuevo.SetField<int>("TIPO_TRANS", VALORES_CUSTODIA.Rows[row.Index].Field<int>("TIPO_TRANS"));
                    nuevo.SetField<string>("RESPONSABLE", VALORES_CUSTODIA.Rows[row.Index].Field<string>("RESPONSABLE"));
                    nuevo.SetField<string>("CATEGORIA", VALORES_CUSTODIA.Rows[row.Index].Field<string>("CATEGORIA"));
                    nuevo.SetField<string>("COD_SUC", VALORES_CUSTODIA.Rows[row.Index].Field<string>("COD_SUC"));
                    nuevo.SetField<string>("UBICACION", VALORES_CUSTODIA.Rows[row.Index].Field<string>("UBICACION"));
                    SELECCION_CUSTODIA.Rows.Add(nuevo);
                }
            }
            lbSeleccionadosCustodia.Text = SELECCION_CUSTODIA.Rows.Count + " SELECCIONADOS";
        }
Beispiel #16
0
        public DataTable loadDb(DataTable table)
        {
            table.Clear();
            try
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SQLite");
                using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = dataSource;
                    using (connection)
                    {
                        // コマンドを作る
                        DbCommand command = connection.CreateCommand();
                        command.CommandText = "SELECT * FROM Alarm";
                        command.CommandType = CommandType.Text;
                        command.Connection = connection;

                        // Dataadapterを作成
                        DbDataAdapter adapter = factory.CreateDataAdapter();
                        adapter.SelectCommand = command;

                        // 読み込み
                        adapter.Fill(table);
                    }
                        return table;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw;
            }
        }
        public DataTable PesquisaGridView(string sCampos, string sWhere)
        {
            try
            {
                DataTable dt = new DataTable();
                string sQuery = "Select "
                                + sCampos
                                + " from conhecim c inner join remetent r on c.cd_remetent = r.cd_remetent"
                                + " Where " + sWhere;

                FbDataAdapter da = new FbDataAdapter(sQuery, cx.get_Conexao());
                dt.Clear();
                cx.Open_Conexao();
                da.Fill(dt);
                da.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cx.Close_Conexao();
            }
        }
        public DataTable PesquisaGridViewContingencia(string sCampos)
        {
            try
            {
                DataTable dt = new DataTable();
                string sQuery = "Select "
                                + sCampos
                                + " from conhecim c inner join remetent r on c.cd_remetent = r.cd_remetent"
                                + " where conhecim.st_contingencia ='S'  and (conhecim.st_cte='N' or  conhecim.st_cte is null)";

                FbDataAdapter da = new FbDataAdapter(sQuery, cx.get_Conexao());
                dt.Clear();
                cx.Open_Conexao();
                da.Fill(dt);
                da.Dispose();

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cx.Close_Conexao();
            }
        }
Beispiel #19
0
 private IDataModel ConvertDataTabel2DataModel(DataTable dt, bool isGlobal)
 {
     try
     {
         var query = from row in dt.AsEnumerable()
                     where row.Field<bool>(SqlFunc4Data.IsGlobal) == isGlobal
                     select row;
         var table = new DataTable();
         table.Clear();
         DataRow dataRow = table.NewRow();
         if (query.Count() != 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 string name = dr.ItemArray[0].ToString();
                 table.Columns.Add(name);
                 dataRow[name] = dr.ItemArray[1];
             }
         }
         return new DataModel(dataRow);
     }
     catch (System.Exception ex)
     {
         Debug.WriteLine(ex.Message);
         return null;
     }
 }
        public void AutoIncrementColumnsOnInsert()
        {
            execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL AUTO_INCREMENT, " +
                "name VARCHAR(100), PRIMARY KEY(id))");
            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

            da.InsertCommand = cb.GetInsertCommand();
            da.InsertCommand.CommandText += "; SELECT last_insert_id()";
            da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

            DataTable dt = new DataTable();
            da.Fill(dt);
            dt.Columns[0].AutoIncrement = true;
            Assert.IsTrue(dt.Columns[0].AutoIncrement);
            dt.Columns[0].AutoIncrementSeed = -1;
            dt.Columns[0].AutoIncrementStep = -1;
            DataRow row = dt.NewRow();
            row["name"] = "Test";

            dt.Rows.Add(row);
            da.Update(dt);

            dt.Clear();
            da.Fill(dt);
            Assert.AreEqual(1, dt.Rows.Count);
            Assert.AreEqual(1, dt.Rows[0]["id"]);
            Assert.AreEqual("Test", dt.Rows[0]["name"]);
            cb.Dispose();
        }
 private void cboYear_SelectedIndexChanged(object sender, EventArgs e)
 {
      try
      {
          string RepPath;
          DataSet dataset = new DataSet();
          ReportDocument RepDoc = new ReportDocument();                 
          RepPath = @"D:\Project\Industrial College\Project1\Source23\Source22\Source21\Pazhohesh\Pazhohesh\Main\CrsThesis1.rpt";                 
          RepDoc.Load(RepPath);                    
          objDataAdapter.SelectCommand = new SqlCommand();
          SqlConnection objConnection = new SqlConnection("Data Source=User-PC;Initial Catalog=ResearchProje1;Integrated Security=True");
          objDataAdapter.SelectCommand.Connection = objConnection;
          DataTable objGozaresh = new DataTable();
          objGozaresh.Clear();
          objDataAdapter.SelectCommand.Parameters.AddWithValue("@DeliveryDate" , cboYear.Text + "%");
          objDataAdapter.SelectCommand.CommandText = "Select * from VwThesisGozaresh1 where DeliveryDate Like @DeliveryDate";
          objDataAdapter.Fill(objGozaresh);
          RepDoc.SetDataSource(objGozaresh);
          crystalReportViewer1.ReportSource = RepDoc;
      }
      catch (Exception ex)
      {
          MessageBox.Show(ex.ToString());
      }
 }
Beispiel #22
0
    protected void Page_Load(object sender, EventArgs e)
    {
        AdminPopedom.IsHoldModel("04");

        string beginTime, EndTime, IPS, DayCount,Mac;
        beginTime = CommonManager.Web.Request("tt",""); //Limit.editCharacter(Limit.getFormValue("tt"));
        EndTime = CommonManager.Web.Request( "nn", "" ); //Limit.editCharacter(Limit.getFormValue("nn"));
        IPS = CommonManager.Web.Request( "ip", "" ); //Limit.editCharacter(Limit.getFormValue("ip"));
        DayCount = CommonManager.Web.Request( "dd", "" ); //Limit.editCharacter(Limit.getFormValue("dd"));
        Mac = CommonManager.Web.Request("mac", "");
        DataTable sd = new DataTable();
        string sql = "select count(*) as bbr,CONVERT(varchar(12),CONVERT(datetime,RegisterTM),110)as ddr from Web_Users where CONVERT(datetime,RegisterTM)>='" + beginTime + "' and CONVERT(datetime,RegisterTM)<='" + EndTime + "' and RegisterIP='" + IPS + "' group by CONVERT(varchar(12),CONVERT(datetime,RegisterTM),110) order by CONVERT(varchar(12),CONVERT(datetime,RegisterTM),110) asc";

        //Response.Write(sql);
        //Response.End();
        sd = DbSession.Default.FromSql( sql ).ToDataTable(); //sqlconn.sqlReader(sql);
        if(sd.Rows.Count>0)
        {
            IpCount.DataSource = sd;
            IpCount.DataBind();
        }
        else
        {
            this.NoIpCount.Visible = true;
        }
        sd.Clear();
    }
 private void button2_Click(object sender, EventArgs e)
 {
     oracle.LinkToOracle("orcl", "hui", "oracle");
     OracleCommand cmd = oracle.getConnection().CreateCommand();
     if (radioButton1.Checked)
     {
         OracleDataAdapter sda = new OracleDataAdapter("select * from department", oracle.getConnection());
         DataTable query = new DataTable();
         query = (DataTable)dataGridView1.DataSource;
         OracleCommandBuilder sb = new OracleCommandBuilder(sda);
         sda.Update(query);
         query.Clear();
         sda.Fill(query);
     }
     if (radioButton2.Checked)
     {
         OracleDataAdapter sda = new OracleDataAdapter("select * from teacher", oracle.getConnection());
         DataTable query = new DataTable();
         query = (DataTable)dataGridView1.DataSource;
         OracleCommandBuilder sb = new OracleCommandBuilder(sda);
         sda.Update(query);
         query.Clear();
         sda.Fill(query);
     }
     oracle.LinkClose();
     cmd.Dispose();
 }
Beispiel #24
0
    protected void Page_Load(object sender, EventArgs e)
    {
        AdminPopedom.IsHoldModel("03");

        if (Request.QueryString["id"] != null)
        {
            id = Request.QueryString["id"].ToString();
            if (!CommonManager.String.IsInteger(id))
            {
                Alert("警告:\\n\\n请勿非法操作!", null);
                return;
            }
            if (!IsPostBack)
            {
                if (Convert.ToInt32(id) > 0)
                {

                    DataTable dt = new DataTable();
                    string sql = "select * from Web_System Where ID=" + id + "";
                    dt = DbSession.Default.FromSql( sql ).ToDataTable(); //sqlconn.sqlReader(sql);
                    if (dt.Rows.Count > 0)
                    {
                        txtParaName.Text = dt.Rows[0]["ParaName"].ToString();
                        txtParaValue.Text = dt.Rows[0]["ParaValue"].ToString();
                        txtParaDesc.Text = dt.Rows[0]["Descript"].ToString();
                    }
                    dt.Clear();
                }
            }
        }
    }
Beispiel #25
0
        public string deletePartnerData(string year, string partnerId)
        {
            string conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
            SqlConnection sqlConn = new SqlConnection();

            sqlConn.ConnectionString = conn;
            DataTable dt = new DataTable();
            dt.Clear();

            SqlCommand cmd = new SqlCommand(string.Format(
            @"DELETE FROM REBATE WHERE YEAR=@YEAR AND ID_PARTNER=@ID_PARTNER"), sqlConn);

            cmd.Parameters.AddWithValue("@YEAR", year);
            cmd.Parameters.AddWithValue("@ID_PARTNER", partnerId);

            try
            {
                sqlConn.Open();
                cmd.ExecuteNonQuery();
                return "The Partner Data specified has been removed sucessfully from the database.";
            }
            catch (Exception e)
            {
                return "error";
            }
            finally
            {
                sqlConn.Close();
            }
        }
Beispiel #26
0
        public string insertDRSI(DRSIForm drsi)
        {
            string Message = string.Empty;
            int result = 0;
            con.Open();
            SqlCommand cmd = new SqlCommand("INSERT INTO [inventory].[dbo].DRSIForm ([CustomerFKID],[ProductFKID],[TotalPcs],[TotalUOM],[TotalWeight],[TotalUnitCost],[TotalCost]) VALUES (@CustomerFKID,@ProductFKID,@TotalPcs,@TotalUOM,@TotalWeight,@TotalUnitCost,@TotalCost)", con);
            cmd.Parameters.AddWithValue("@CustomerFKID", drsi.CustomerFKID);
            cmd.Parameters.AddWithValue("@ProductFKID", drsi.ProductFKID);
            cmd.Parameters.AddWithValue("@TotalPcs", drsi.TotalPcs);
            cmd.Parameters.AddWithValue("@TotalUOM", drsi.TotalUOM);
            cmd.Parameters.AddWithValue("@TotalWeight", drsi.TotalWeight);
            cmd.Parameters.AddWithValue("@TotalUnitCost", drsi.TotalUnitCost);
            cmd.Parameters.AddWithValue("@TotalCost", drsi.TotalCost);
            result = cmd.ExecuteNonQuery();
            con.Close();
            string query ="select top 1 ID from [DRSIForm] order by id desc";
            DataTable dt = new DataTable("Customer");
            dt.Clear();
            dt = DataAccess.DBAdapter.GetRecordSet(query);

            if (dt.Rows.Count > 0)
            {
                Message = dt.Rows[0]["ID"].ToString();
            }
            return Message;
        }
Beispiel #27
0
    protected void Page_Load(object sender, EventArgs e)
    {
        AdminPopedom.IsHoldModel("11");

        string tmp = CommonManager.Web.Request( "id", "" );
        if( string.IsNullOrEmpty( tmp ) || !CommonManager.String.IsInteger( tmp ) )
        {
            Alert( "请勿非法操作!", null );
            return;
        }
        else
            Logid = tmp; //Limit.editCharacter(Limit.getFormValue("id"));

        DataTable sd = new DataTable();
        string sql = "select *,b.Award_Name,c.UserName from Web_AwardLog a,Web_Award b,TUsers c "
                        +"where a.Award_ID=b.Award_ID and a.UserID=c.UserID and log_id=" + Logid + "";
        sd = DbSession.Default.FromSql( sql ).ToDataTable(); //sqlconn.sqlReader(sql);
        if (sd.Rows.Count > 0)
        {
            PName = sd.Rows[0]["Award_Name"].ToString();
            PUser = sd.Rows[0]["UserName"].ToString();
            Ptime = sd.Rows[0]["AwardTime"].ToString();
            PNameUser = sd.Rows[0]["TrueName"].ToString();
            Ptel = sd.Rows[0]["Phone"].ToString();
            Paddress = sd.Rows[0]["Address"].ToString();
            PIsCash = sd.Rows[0]["IsCash"].ToString();
            PUserRemark = sd.Rows[0]["UserRemark"].ToString();
        }
        sd.Clear();
    }
Beispiel #28
0
        public void NadjiArtikal(DataTable artikal, Int32 artikal_ID)
        {
            SqlConnection _konekcijaSqlConnection = new SqlConnection();
            using (_konekcijaSqlConnection = Konekcija.DajKonekciju())
            {
                SqlDataAdapter _nadjiArtikalSQLDataAdapter = new SqlDataAdapter();
                SqlCommand _nadjiArtikalSQLCommand = new SqlCommand();

                _nadjiArtikalSQLCommand.CommandText = " SELECT vwArtikal.Artikal_ID, vwArtikal.Proizvođač, vwArtikal.[Broj proizvoda], vwArtikal.[Naziv proizvoda], vwArtikal.[Cena ugradnje], vwArtikal.[Norma sati], vwArtikal.Cena, vwArtikal.[Najpovoljniji dobavljač], vwArtikal.[Korisnik programa], vwArtikal.Napomena from vwArtikal where Artikal_ID = " + artikal_ID;

                _nadjiArtikalSQLCommand.Connection = _konekcijaSqlConnection;

                _nadjiArtikalSQLDataAdapter.SelectCommand = _nadjiArtikalSQLCommand;

                //isprazni tabelu
                artikal.Clear();

                //napuni tabelu
                try
                {
                    _nadjiArtikalSQLDataAdapter.Fill(artikal);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
 /////////////////////// بارگذاری داده های بانک /////////////////////
 public void LoadData()
 {
     try
     {
         DataTable objT3 = new DataTable();
         objT3.Clear();
         objDataAdapter.SelectCommand = new SqlCommand();
         objDataAdapter.SelectCommand.Connection = FrmEnter.objConnection;
         objDataAdapter.SelectCommand.CommandText = "SELECT SessionID , SessionNumber , SessionDate , SessionDay , TedadMadovin , TedadGhaebin , SessionSubject , SessionTime , Hadaya , SessionLoc , SessionOrder , SessionMotafaregheh , SessionMosavab FROM Tbl_Session";
         objDataAdapter.Fill(objT3);
         dataGridView1.DataSource = objT3;
         dataGridView1.Columns[0].HeaderText = " کد جلسه";
         dataGridView1.Columns[1].HeaderText = "شماره جلسه";
         dataGridView1.Columns[2].HeaderText = "تاریخ جلسه";
         dataGridView1.Columns[3].HeaderText = "روز";
         dataGridView1.Columns[4].HeaderText = "تعداد مدعوین";
         dataGridView1.Columns[5].HeaderText = "تعداد غایبین";
         dataGridView1.Columns[6].HeaderText = "موضوع";
         dataGridView1.Columns[7].HeaderText = "ساعت حضور";
         dataGridView1.Columns[8].HeaderText = "هدایا";
         dataGridView1.Columns[9].HeaderText = "محل جلسه";
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.ToString());
     }
 }
    public static void getAllEmployees()
    {
        if (empAdapter == null)
        {
            setupEmpAdapter();
        }

        empCmd.CommandText = "Select * from Employee order by EmpID";

        try
        {
            if (!(tblEmployee == null))
            {
                tblEmployee.Clear();
            }
            empAdapter.Fill(tblEmployee);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            con.Close();
        }
    }
Beispiel #31
0
 private void carregarDttUserCampo()
 {
     dtUserCampo = user.getCamposUsuario();
     dtAtual = dtUserCampo.Clone();
     dtAtual.Clear();
     dtgvCampos.DataSource = dtAtual;
 }
    public static void getAllProducts()
    {
        if (prodAdapter == null)
        {
            setupProdAdapter();
        }

        prodCmd.CommandText = "Select * from Product order by ProductID";

        try
        {
            if (!(tblProduct == null))
            {
                tblProduct.Clear();
            }
            prodAdapter.Fill(tblProduct);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            con.Close();
        }
    }
Beispiel #33
0
    public DataTable ReadUserInfo(string str)
    {
        if (bll.GetCookie() == null)
        {
            return null;
        }
        else
        {
            int UserId = bll.GetCookie().UserID;

            DataTable dt = new DataTable();
            try
            {
                dr = bll.GetUserAllInfo(UserId);
                dt = dr.Table.Copy();
                dt.Clear();
                dt.ImportRow(dr);
            }
            catch (Exception ex)
            {
                Response.Write(ex);
            }
            return dt;
        }
    }
Beispiel #34
0
        private void 测试记录_Load(object sender, EventArgs e)
        {
            string          connStr;
            OleDbConnection conn;

            connStr =
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 登录.Filename_数据文件 + ";Jet OLEDB:Engine Type=5";
            conn = new OleDbConnection(connStr);
            conn.Open();

            System.Data.OleDb.OleDbDataAdapter da = new OleDbDataAdapter(数据库参数.sql_测试记录, 数据库参数.mycon);
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Clear();
            conn.Close();

            try
            {
                da.Fill(dt);  //将数据库内容填充到dt 这个DataTable(数据表)中
                DataTable datNew = new DataTable();
                datNew.Clear();
                datNew = dt.DefaultView.ToTable(false, new string[] { "ID", "呼吸器编号", "测试时间", "测试人员", "测试结果", "型号", "制造商" });
                dataGridView1.DataSource = null;
                if (datNew.Rows.Count >= 1)
                {
                    dataGridView1.DataSource = datNew;
                }
                else
                {
                    dataGridView1.DataSource = null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
    protected void Limpiar(object sender, EventArgs e)
    {
        GVinfprof.Visible = false;
        Linfo.Text        = "";
        Tbotones2.Visible = false;
        Tcarta.Visible    = false;
        DDLlista.Enabled  = true;
        DDLlista.Items.Clear();
        string sql = "";

        if (RBexterno.Checked)
        {
            sql = "select u.usu_username ,CONCAT(CONCAT(u.usu_nombre, ' '), u.usu_apellido) from usuario_rol e, usuario u where u.usu_username = E.Usu_Username and u.USU_ESTADO = 'ACTIVO' and E.Rol_Id = 'EXT'";
        }
        else
        {
            sql = "Select u.usu_username ,CONCAT(CONCAT(u.usu_nombre, ' '), u.usu_apellido)from profesor p, usuario u where u.usu_username=p.usu_username and u.USU_ESTADO='ACTIVO'";
        }
        DDLlista.Items.AddRange(con.cargardatos(sql));
        table = (System.Data.DataTable)(Session["Tabla"]);
        table.Clear();
        Bconsulta.Visible = true;
        Blimpiar.Visible  = false;
    }
Beispiel #36
0
 private void button_clear_Click(object sender, EventArgs e)
 {
     table.Clear();
     curr_id = 0;
 }
        private void Import(string txtFileName)
        {
            if (txtFileName != string.Empty)
            {
                try
                {
                    String name   = "Sheet1";  // default Sheet1
                    String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                    txtFileName +
                                    ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

                    OleDbConnection con   = new OleDbConnection(constr);
                    OleDbCommand    oconn = new OleDbCommand("Select * From [" + name + "$]", con);
                    con.Open();

                    OleDbDataAdapter      sda = new OleDbDataAdapter(oconn);
                    System.Data.DataTable data = new System.Data.DataTable();
                    int value = 0; int newrow = 0;
                    sda.Fill(data);
                    dtnonadded.Clear();
                    dtnonadded.Columns.Clear();
                    dtnonadded.Columns.Add("State", typeof(string));
                    dtnonadded.Columns.Add("County", typeof(string));
                    dtnonadded.Columns.Add("CountyType", typeof(string));


                    for (int i = 0; i < data.Rows.Count; i++)
                    {
                        if (data.Rows[i]["State"].ToString() != "" || data.Rows[i]["County"].ToString() != "" || data.Rows[i]["State"].ToString() != null || data.Rows[i]["County"].ToString() != null && data.Rows[i]["CountyType"].ToString() != "" && data.Rows[i]["CountyType"].ToString() != null)
                        {
                            grd_Client_cost.Rows.Add();
                            grd_Client_cost.Rows[i].Cells[0].Value = data.Rows[i]["State"].ToString();
                            grd_Client_cost.Rows[i].Cells[1].Value = data.Rows[i]["County"].ToString();
                            grd_Client_cost.Rows[i].Cells[2].Value = data.Rows[i]["CountyType"].ToString();



                            //Error State
                            state      = data.Rows[i]["State"].ToString();
                            county     = data.Rows[i]["County"].ToString();
                            countytype = data.Rows[i]["CountyType"].ToString();
                            Hashtable             htstate = new Hashtable();
                            System.Data.DataTable dtstate = new System.Data.DataTable();
                            htstate.Add("@Trans", "STATE_ID");
                            htstate.Add("@State", state);
                            dtstate = dataaccess.ExecuteSP("Sp_County", htstate);
                            if (dtstate.Rows.Count != 0)
                            {
                                State_id = int.Parse(dtstate.Rows[0]["State_ID"].ToString());
                                grd_Client_cost.Rows[i].Cells[3].Value = State_id;
                            }
                            else
                            {
                                // MessageBox.Show(state + " does not exist in State Info");
                                State_id = 0;
                                grd_Client_cost.Rows[i].Cells[3].Value             = State_id;
                                grd_Client_cost.Rows[i].DefaultCellStyle.BackColor = Color.Red;
                            }

                            //Error County
                            Hashtable             htcounty = new Hashtable();
                            System.Data.DataTable dtcounty = new System.Data.DataTable();
                            htcounty.Add("@Trans", "COUNTY_ID");
                            htcounty.Add("@State_Id", State_id);
                            htcounty.Add("@County", county);
                            dtcounty = dataaccess.ExecuteSP("Sp_County", htcounty);
                            if (dtcounty.Rows.Count != 0)
                            {
                                County_id = int.Parse(dtcounty.Rows[0]["County_ID"].ToString());
                                grd_Client_cost.Rows[i].Cells[4].Value = County_id;
                            }
                            else
                            {
                                County_id = 0;
                                //  MessageBox.Show(county + " does not exist in County Info");

                                grd_Client_cost.Rows[i].Cells[4].Value = County_id;
                                // grd_Client_cost.Rows[i].DefaultCellStyle.BackColor = Color.Red;
                            }

                            //error_County_Type

                            Hashtable             htcountyType = new Hashtable();
                            System.Data.DataTable dtcountyType = new System.Data.DataTable();
                            htcountyType.Add("@Trans", "CHECK_COUNTY_TYPE");
                            htcountyType.Add("@State_County_Type", countytype);

                            dtcountyType = dataaccess.ExecuteSP("Sp_External_Client_Sate_County", htcountyType);
                            if (dtcounty.Rows.Count != 0)
                            {
                                County_Type_Id = int.Parse(dtcountyType.Rows[0]["Order_Assign_Type_Id"].ToString());
                                grd_Client_cost.Rows[i].Cells[6].Value = County_Type_Id;
                            }
                            else
                            {
                                County_Type_Id = 0;
                                //  MessageBox.Show(county + " does not exist in County Info");

                                grd_Client_cost.Rows[i].Cells[6].Value             = County_Type_Id;
                                grd_Client_cost.Rows[i].DefaultCellStyle.BackColor = Color.Red;
                            }

                            //Check Exist State and county



                            if (State_id == 0)
                            {
                                grd_Client_cost.Rows[i].Cells[5].Value = "Error";
                                dr = dtnonadded.NewRow();

                                dr["State"]  = grd_Client_cost.Rows[i].Cells[0].Value.ToString();
                                dr["County"] = grd_Client_cost.Rows[i].Cells[1].Value.ToString();

                                dr["CountyType"] = grd_Client_cost.Rows[i].Cells[2].Value.ToString();
                                dtnonadded.Rows.Add(dr);
                                grd_Client_cost.Rows[i].DefaultCellStyle.BackColor = Color.Red;
                            }

                            else
                            {
                                grd_Client_cost.Rows[i].Cells[5].Value = "NoError";
                            }

                            if (State_id != 0)
                            {
                                Hashtable             htchek  = new Hashtable();
                                System.Data.DataTable dtcheck = new System.Data.DataTable();

                                htchek.Add("@Trans", "CHECK_COUNTY_BY_NAME");
                                htchek.Add("@State_Id", State_id);
                                htchek.Add("@County", county);
                                dtcheck = dataaccess.ExecuteSP("Sp_County", htchek);

                                if (dtcheck.Rows.Count > 0)
                                {
                                    County_Check = int.Parse(dtcheck.Rows[0]["count"].ToString());
                                }
                                else
                                {
                                    County_Check = 0;
                                }
                            }
                            if (County_Check > 0)
                            {
                                grd_Client_cost.Rows[i].DefaultCellStyle.BackColor = Color.LightYellow;
                            }


                            if (County_Type_Id == 0)
                            {
                                grd_Client_cost.Rows[i].Cells[5].Value = "Error";
                                dr = dtnonadded.NewRow();

                                dr["State"]  = grd_Client_cost.Rows[i].Cells[0].Value.ToString();
                                dr["County"] = grd_Client_cost.Rows[i].Cells[1].Value.ToString();

                                dr["CountyType"] = grd_Client_cost.Rows[i].Cells[2].Value.ToString();
                                dtnonadded.Rows.Add(dr);
                                grd_Client_cost.Rows[i].DefaultCellStyle.BackColor = Color.Red;
                            }
                            else
                            {
                                grd_Client_cost.Rows[i].Cells[5].Value = "NoError";
                            }
                        }
                        else
                        {
                            grd_Client_cost.Rows.Clear();
                            MessageBox.Show("Check Empty Cells in Excel");
                        }
                    }
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }
        }
Beispiel #38
0
        private void Grid_User_Summary_Click(object sender, EventArgs e)
        {
            SplashScreenManager.ShowForm(this, typeof(Ordermanagement_01.Masters.WaitForm1), true, true, false);
            try
            {
                var columnIndex = gridView2.FocusedColumn.VisibleIndex;

                if (columnIndex == 2)
                {
                    //System.Data.DataRow row = gridView2.GetDataRow(gridView2.FocusedRowHandle);
                    //int Order_ID = int.Parse(row["Order_Id"].ToString());



                    Hashtable             ht_get = new Hashtable();
                    System.Data.DataTable dt_get = new System.Data.DataTable();
                    ht_get.Clear();
                    dt_get.Clear();

                    string date = dt_get_singale_date_wise.Rows[0]["Date"].ToString();
                    if (branch_Id == 0 && shift_Type_Id == 0)
                    {
                        ht_get.Add("@Trans", "LOGGED_IN_USER");
                    }
                    else if (branch_Id != 0 && shift_Type_Id == 0)
                    {
                        ht_get.Add("@Trans", "LOGGED_IN_USER_BRANCH_WISE");
                    }
                    else if (branch_Id != 0 && shift_Type_Id != 0)
                    {
                        ht_get.Add("@Trans", "LOGGED_IN_USER_BRANCH_SHIFT_WISE");
                    }
                    ht_get.Add("@date", date);
                    ht_get.Add("@Branch", branch_Id);
                    ht_get.Add("@Shift_Type_Id", shift_Type_Id);
                    dt_get = dataaccess.ExecuteSP("Sp_Daily_Status_Report", ht_get);

                    User_Details_View view = new User_Details_View(dt_get);
                    view.Show();
                }
                else if (columnIndex == 4)
                {
                    //System.Data.DataRow row = gridView2.GetDataRow(gridView2.FocusedRowHandle);
                    //int Order_ID = int.Parse(row["Order_Id"].ToString());



                    Hashtable             ht_get_1 = new Hashtable();
                    System.Data.DataTable dt_get_1 = new System.Data.DataTable();
                    ht_get_1.Clear();
                    dt_get_1.Clear();

                    string date = dt_get_singale_date_wise.Rows[0]["Date"].ToString();
                    if (branch_Id == 0 && shift_Type_Id == 0)
                    {
                        ht_get_1.Add("@Trans", "PRODUCTION_USER");
                    }
                    else if (branch_Id != 0 && shift_Type_Id == 0)
                    {
                        ht_get_1.Add("@Trans", "PRODUCTION_USER_BRANCH_WISE");
                    }
                    else if (branch_Id != 0 && shift_Type_Id != 0)
                    {
                        ht_get_1.Add("@Trans", "PRODUCTION_USER_BRANCH_SHIFT_WISE");
                    }
                    ht_get_1.Add("@Branch", branch_Id);
                    ht_get_1.Add("@Shift_Type_Id", shift_Type_Id);
                    // ht_get_1.Add("@date", date);
                    dt_get_1 = dataaccess.ExecuteSP("Sp_Daily_Status_Report", ht_get_1);

                    User_Details_View view = new User_Details_View(dt_get_1);
                    view.Show();
                }
            }
            catch (Exception ex)
            {
                //Close Wait Form
                SplashScreenManager.CloseForm(false);
                MessageBox.Show("Error Occured Please Check With Administrator");
            }
            finally
            {
                //Close Wait Form
                SplashScreenManager.CloseForm(false);
            }
        }
Beispiel #39
0
        private void Updated_Score_Board()
        {
            SplashScreenManager.ShowForm(this, typeof(Ordermanagement_01.Masters.WaitForm1), true, true, false);
            try
            {
                gridControlScoreBoard.DataSource = null;
                gridViewScoreBoard.Columns.Clear();
                DateTime d1 = DateTime.Now;
                d1 = d1.AddDays(-1);
                Hashtable ht_Score1 = new Hashtable();

                System.Data.DataTable dt_Score1 = new System.Data.DataTable();
                dt_Score1.Clear();

                Hashtable             ht_Get_User_Orders = new Hashtable();
                System.Data.DataTable dt_Get_User_Orders = new System.Data.DataTable();

                if (userRoleId == 2)
                {
                    ht_Get_User_Orders.Add("@Trans", "CALCUATE_MONTHLY_WISE_USER_EFFECINECY_BY_USER_ID_SCORE_BOARD2");
                }
                else
                {
                    ht_Get_User_Orders.Add("@Trans", "CALCUATE_MONTHLY_WISE_USER_EFFECINECY_FOR_SCORE_BOARD2");
                }

                ht_Get_User_Orders.Add("@User_Id", userId);
                ht_Get_User_Orders.Add("@Month", lookUpEditMonth.EditValue.ToString()); //ddl_Month.SelectedValue.ToString()
                ht_Get_User_Orders.Add("@Years", lookUpEditYear.EditValue.ToString());  //ddl_Year.SelectedValue.ToString()
                dt_Get_User_Orders = dataaccess.ExecuteSP("Sp_Score_Board", ht_Get_User_Orders);


                Hashtable ht_Score = new Hashtable();

                System.Data.DataTable dt_Score  = new System.Data.DataTable();
                System.Data.DataTable dt_Score2 = new System.Data.DataTable();
                dt_Score2.Clear();



                if (userRoleId == 2)
                {
                    ht_Score.Add("@Trans", "GET_USER_NEWLY_UPDATED_ORDER_EFFECINECY_USER_ID_WISE");
                }
                else
                {
                    // Insert to temp table data
                    Hashtable ht_Temp_Score = new Hashtable();

                    System.Data.DataTable dt_Temp_Score = new System.Data.DataTable();
                    ht_Temp_Score.Add("@Trans", "INSERT_EFF_TO_TEMP");
                    dt_Temp_Score = dataaccess.ExecuteSP("Sp_Score_Board", ht_Temp_Score);

                    if (checkEditProductionTimeWise.Checked == true)
                    {
                        ht_Score.Add("@Trans", "GET_USER_UPDATED_NEW_ORDER_EFFECINECY");
                    }
                    else if (checkEditTargetWise.Checked == true)
                    {
                        ht_Score.Add("@Trans", "GET_USER_UPDATED_TARGET_WISE_ORDER_EFFECINECY");
                    }
                }

                ht_Score.Add("@User_Id", userId);
                dt_Score2 = dataaccess.ExecuteSP("Sp_Score_Board", ht_Score);

                Hashtable             htget_Avg_Total_eff = new Hashtable();
                System.Data.DataTable dtget_Avg_Total_Eff = new System.Data.DataTable();

                if (userRoleId == 2)
                {
                    htget_Avg_Total_eff.Add("@Trans", "GET_NEWLY_UPDATED_AVG_EFF_BY_USER_ID");
                }
                else
                {
                    if (checkEditProductionTimeWise.Checked == true)
                    {
                        htget_Avg_Total_eff.Add("@Trans", "GET_NEWLY_UPDATED_AVG_EFF");
                    }
                    else if (checkEditTargetWise.Checked == true)
                    {
                        htget_Avg_Total_eff.Add("@Trans", "GET_NEWLY_TARGETED_UPDATED_AVG_EFF");
                    }
                }
                htget_Avg_Total_eff.Add("@User_Id", userId);
                dtget_Avg_Total_Eff = dataaccess.ExecuteSP("Sp_Score_Board", htget_Avg_Total_eff);

                System.Data.DataTable dt_Final_Score1 = new System.Data.DataTable();

                var collection = from t1 in dt_Score2.AsEnumerable()
                                 join t2 in dtget_Avg_Total_Eff.AsEnumerable()
                                 on t1["User_Id"] equals t2["User_Id"]
                                 select new
                {
                    User_Id         = t1["User_Id"],
                    User_Name       = t1["User_Name"],
                    Branch_Name     = t1["Branch_Name"],
                    DRN_Emp_Code    = t1["DRN_Emp_Code"],
                    Emp_Job_Role    = t1["Emp_Job_Role"],
                    Operation_Type  = t1["Operation_Type"],
                    Shift_Type_Name = t1["Shift_Type_Name"],
                    Reporting_To_1  = t1["Reporting_To_1"],
                    Reporting_To_2  = t1["Reporting_To_2"],
                    Avg_Eff         = t2["Avg_Eff"],
                    D1  = t1["1"],
                    D2  = t1["2"],
                    D3  = t1["3"],
                    D4  = t1["4"],
                    D5  = t1["5"],
                    D6  = t1["6"],
                    D7  = t1["7"],
                    D8  = t1["8"],
                    D9  = t1["9"],
                    D10 = t1["10"],
                    D11 = t1["11"],
                    D12 = t1["12"],
                    D13 = t1["13"],
                    D14 = t1["14"],
                    D15 = t1["15"],
                    D16 = t1["16"],
                    D17 = t1["17"],
                    D18 = t1["18"],
                    D19 = t1["19"],
                    D20 = t1["20"],
                    D21 = t1["21"],
                    D22 = t1["22"],
                    D23 = t1["23"],
                    D24 = t1["24"],
                    D25 = t1["25"],
                    D26 = t1["26"],
                    D27 = t1["27"],
                    D28 = t1["28"],
                    D29 = t1["29"],
                    D30 = t1["30"],
                    D31 = t1["31"],
                };
                System.Data.DataTable result = new System.Data.DataTable("Final_Data");
                result.Columns.Add("User_Id", typeof(string));
                result.Columns.Add("User_Name", typeof(string));
                result.Columns.Add("Branch_Name", typeof(string));
                result.Columns.Add("DRN_Emp_Code", typeof(string));
                result.Columns.Add("Emp_Job_Role", typeof(string));
                result.Columns.Add("Operation_Type", typeof(string));
                result.Columns.Add("Shift_Type_Name", typeof(string));
                result.Columns.Add("Reporting_To_1", typeof(string));
                result.Columns.Add("Reporting_To_2", typeof(string));
                result.Columns.Add("Avg_Eff", typeof(string));

                System.Data.DataTable dt_Final_Score        = new System.Data.DataTable();
                Hashtable             htgetfirst_Last_Dates = new Hashtable();
                System.Data.DataTable dtgetfirst_last_Dates = new System.Data.DataTable();

                htgetfirst_Last_Dates.Add("@Trans", "GET_FIRST_LAST_DATE");
                htgetfirst_Last_Dates.Add("@Month", int.Parse(lookUpEditMonth.EditValue.ToString()));
                htgetfirst_Last_Dates.Add("@Years", int.Parse(lookUpEditYear.EditValue.ToString()));
                dtgetfirst_last_Dates = dataaccess.ExecuteSP("Sp_Score_Board", htgetfirst_Last_Dates);

                Hashtable htdatecolumn = new Hashtable();

                System.Data.DataTable dtdatecolumn = new System.Data.DataTable();
                htdatecolumn.Add("@Trans", "GET_DATES");
                htdatecolumn.Add("@date_from1", dtgetfirst_last_Dates.Rows[0]["First_Date"].ToString());
                htdatecolumn.Add("@date_to1", dtgetfirst_last_Dates.Rows[0]["Last_Date"].ToString());
                dtdatecolumn = dataaccess.ExecuteSP("Sp_Score_Board", htdatecolumn);
                foreach (DataRow row in dtdatecolumn.Rows)
                {
                    result.Columns.Add(row["dts"].ToString(), typeof(object));
                }
                if (lookUpEditMonth.EditValue.ToString() == "2")
                {
                    if (result.Columns.Contains("29/02"))
                    {
                        foreach (var item in collection)
                        {
                            result.Rows.Add(item.User_Id, item.User_Name, item.Branch_Name, item.DRN_Emp_Code, item.Emp_Job_Role, item.Operation_Type, item.Shift_Type_Name, item.Reporting_To_1, item.Reporting_To_2, item.Avg_Eff, item.D1, item.D2, item.D3, item.D4, item.D5, item.D6, item.D7, item.D8, item.D9, item.D10,
                                            item.D11, item.D12, item.D13, item.D14, item.D15, item.D16, item.D17, item.D18, item.D19, item.D20, item.D21, item.D22, item.D23, item.D24, item.D25, item.D26, item.D27, item.D28, item.D29);
                        }
                    }
                    else
                    {
                        foreach (var item in collection)
                        {
                            result.Rows.Add(item.User_Id, item.User_Name, item.Branch_Name, item.DRN_Emp_Code, item.Emp_Job_Role, item.Operation_Type, item.Shift_Type_Name, item.Reporting_To_1, item.Reporting_To_2, item.Avg_Eff, item.D1, item.D2, item.D3, item.D4, item.D5, item.D6, item.D7, item.D8, item.D9, item.D10,
                                            item.D11, item.D12, item.D13, item.D14, item.D15, item.D16, item.D17, item.D18, item.D19, item.D20, item.D21, item.D22, item.D23, item.D24, item.D25, item.D26, item.D27, item.D28);
                        }
                    }
                }
                else
                {
                    if (result.Columns.Contains("31/" + GetDigitAppended(lookUpEditMonth.EditValue.ToString())))
                    {
                        foreach (var item in collection)
                        {
                            result.Rows.Add(item.User_Id, item.User_Name, item.Branch_Name, item.DRN_Emp_Code, item.Emp_Job_Role, item.Operation_Type, item.Shift_Type_Name, item.Reporting_To_1, item.Reporting_To_2, item.Avg_Eff, item.D1, item.D2, item.D3, item.D4, item.D5, item.D6, item.D7, item.D8, item.D9, item.D10,
                                            item.D11, item.D12, item.D13, item.D14, item.D15, item.D16, item.D17, item.D18, item.D19, item.D20, item.D21, item.D22, item.D23, item.D24, item.D25, item.D26, item.D27, item.D28, item.D29, item.D30, item.D31);
                        }
                    }
                    else
                    {
                        foreach (var item in collection)
                        {
                            result.Rows.Add(item.User_Id, item.User_Name, item.Branch_Name, item.DRN_Emp_Code, item.Emp_Job_Role, item.Operation_Type, item.Shift_Type_Name, item.Reporting_To_1, item.Reporting_To_2, item.Avg_Eff, item.D1, item.D2, item.D3, item.D4, item.D5, item.D6, item.D7, item.D8, item.D9, item.D10,
                                            item.D11, item.D12, item.D13, item.D14, item.D15, item.D16, item.D17, item.D18, item.D19, item.D20, item.D21, item.D22, item.D23, item.D24, item.D25, item.D26, item.D27, item.D28, item.D29, item.D30);
                        }
                    }
                }

                if (result.Rows.Count > 0)
                {
                    gridControlScoreBoard.DataSource = result;
                    gridViewScoreBoard.Columns.ColumnByFieldName("User_Id").Visible = false;
                    gridViewScoreBoard.Columns.ColumnByFieldName("User_Name").OptionsColumn.AllowFocus = false;
                    gridViewScoreBoard.BestFitColumns();
                    gridViewScoreBoard.ShowFindPanel();
                    foreach (GridColumn column in ((DevExpress.XtraGrid.Views.Base.ColumnView)gridControlScoreBoard.Views[0]).Columns)
                    {
                        if (column.FieldName == "User_Name")
                        {
                            column.Fixed = FixedStyle.Left;
                        }
                        if (column.FieldName == "Avg_Eff")
                        {
                            column.FilterMode = ColumnFilterMode.DisplayText;
                            column.SortMode   = ColumnSortMode.DisplayText;
                            column.OptionsColumn.AllowSort = DefaultBoolean.True;
                        }
                        if (column.FieldName != "User_Name" && column.FieldName != "Branch_Name" && column.FieldName != "DRN_Emp_Code" &&
                            column.FieldName != "Emp_Job_Role" && column.FieldName != "Shift_Type_Name" && column.FieldName != "Reporting_To_1" &&
                            column.FieldName != "Reporting_To_2" && column.FieldName != "Avg_Eff" && column.FieldName != "Operation_Type")
                        {
                            RepositoryItemHyperLinkEdit edit = new RepositoryItemHyperLinkEdit();
                            edit.Appearance.ForeColor            = Color.Blue;
                            edit.Appearance.Options.UseForeColor = true;
                            column.ColumnEdit = edit;
                            column.FilterMode = ColumnFilterMode.DisplayText;
                            column.SortMode   = ColumnSortMode.DisplayText;
                            column.OptionsColumn.AllowSort = DefaultBoolean.True;
                        }
                    }
                }
                else
                {
                    SplashScreenManager.CloseForm(false);
                    XtraMessageBox.Show("Data not found");
                }
            }
            catch (Exception e)
            {
                SplashScreenManager.CloseForm(false);
                XtraMessageBox.Show("Something went wrong check with admin");
            }
            finally
            {
                SplashScreenManager.CloseForm(false);
            }
        }
Beispiel #40
0
        private void laydanhsachpheduyet()
        {
            DataTable dt       = new DataTable();
            String    sCommand = "";

            //sCommand = "select khachhang.MAKH,khachhang.HOTEN,sum(lichsudiem.diem) as diem,pheduyet from LICHSUDIEM,KHACHHANG where lichsudiem.MAKH=KHACHHANG.MAKH and left(Lichsudiem.makh,4)='" + Thongtindangnhap.macn + "' and lichsudiem.thang='"+dtpFrom.Text+"' group by LICHSUDIEM.MAKH,khachhang.MAKH,khachhang.HOTEN,pheduyet";
            if (optTatca.Checked == true)
            {
                sCommand = "select lichsudiem.thang,khachhang.HOTEN,khachhang.dienthoai1,khachhang.ngaysinh,khachhang.CMND,LICHSUDIEM.MAKH, diem_cn.diem as diemthangtruoc,sum(LICHSUDIEM.DIEM) as diem,LICHSUDIEM.PHEDUYET from LICHSUDIEM left join diem_cn on lichsudiem.makh=diem_cn.makh inner join khachhang on lichsudiem.makh=khachhang.makh group by LICHSUDIEM.MAKH,LICHSUDIEM.PHEDUYET,diem_cn.DIEM,LICHSUDIEM.THANG,khachhang.hoten,khachhang.loaikh,khachhang.dienthoai1,khachhang.ngaysinh,khachhang.CMND having khachhang.loaikh=1 and LEFT(lichsudiem.makh,4)='" + Thongtindangnhap.macn + "' and lichsudiem.thang=N'" + dtpFrom.Text + "'";
            }
            if (optPD.Checked == true)
            {
                sCommand = "select lichsudiem.thang,khachhang.HOTEN,khachhang.dienthoai1,khachhang.ngaysinh,khachhang.CMND,LICHSUDIEM.MAKH, diem_cn.diem as diemthangtruoc,sum(LICHSUDIEM.DIEM) as diem,LICHSUDIEM.PHEDUYET from LICHSUDIEM left join diem_cn on lichsudiem.makh=diem_cn.makh inner join khachhang on lichsudiem.makh=khachhang.makh group by LICHSUDIEM.MAKH,LICHSUDIEM.PHEDUYET,diem_cn.DIEM,LICHSUDIEM.THANG,khachhang.hoten,khachhang.loaikh,khachhang.dienthoai1,khachhang.ngaysinh,khachhang.CMND having lichsudiem.pheduyet=1 and khachhang.loaikh=1 and LEFT(lichsudiem.makh,4)='" + Thongtindangnhap.macn + "' and lichsudiem.thang=N'" + dtpFrom.Text + "'";
            }
            if (optCPD.Checked == true)
            {
                sCommand = "select lichsudiem.thang,khachhang.HOTEN,khachhang.dienthoai1,khachhang.ngaysinh,khachhang.CMND,LICHSUDIEM.MAKH, diem_cn.diem as diemthangtruoc,sum(LICHSUDIEM.DIEM) as diem,LICHSUDIEM.PHEDUYET from LICHSUDIEM left join diem_cn on lichsudiem.makh=diem_cn.makh inner join khachhang on lichsudiem.makh=khachhang.makh group by LICHSUDIEM.MAKH,LICHSUDIEM.PHEDUYET,diem_cn.DIEM,LICHSUDIEM.THANG,khachhang.hoten,khachhang.loaikh,khachhang.dienthoai1,khachhang.ngaysinh,khachhang.CMND having lichsudiem.pheduyet=0 and khachhang.loaikh=1 and LEFT(lichsudiem.makh,4)='" + Thongtindangnhap.macn + "' and lichsudiem.thang=N'" + dtpFrom.Text + "'";
            }


            if (DataAccess.conn.State == ConnectionState.Open)
            {
                DataAccess.conn.Close();
            }
            DataAccess.conn.Open();
            new SqlDataAdapter(sCommand, DataAccess.conn).Fill(dt);
            DataAccess.conn.Close();

            System.Data.DataTable dskh = new System.Data.DataTable();
            dskh.Clear();
            DataColumn col = null;

            col = new DataColumn("STT", typeof(int));
            dskh.Columns.Add(col);
            col = new DataColumn("Mã", typeof(string));
            dskh.Columns.Add(col);
            col = new DataColumn("Họ tên", typeof(string));
            dskh.Columns.Add(col);
            col = new DataColumn("Điểm tháng trước", typeof(decimal));
            dskh.Columns.Add(col);
            col = new DataColumn("Số điểm", typeof(decimal));
            dskh.Columns.Add(col);
            col = new DataColumn("Tổng điểm", typeof(decimal));
            dskh.Columns.Add(col);
            col = new DataColumn("Phê duyệt", typeof(bool));
            dskh.Columns.Add(col);
            col = new DataColumn("SĐT", typeof(string));
            dskh.Columns.Add(col);
            col = new DataColumn("Ngày sinh", typeof(string));
            dskh.Columns.Add(col);
            col = new DataColumn("CMND", typeof(string));
            dskh.Columns.Add(col);

            int iRows = dt.Rows.Count;
            int temp  = 0;

            if (iRows > 0)
            {
                for (int i = 0; i < iRows; i++)
                {
                    //if (dt.Rows[i]["thang"].ToString() == dtpFrom.Text)
                    //{
                    DataRow row = dskh.NewRow();
                    temp   = temp + 1;
                    row[0] = temp;
                    row[1] = dt.Rows[i]["makh"].ToString();
                    row[2] = dt.Rows[i]["hoten"].ToString();
                    if (dt.Rows[i]["diemthangtruoc"].ToString() == "")
                    {
                        row[3] = 0;
                    }
                    else
                    {
                        row[3] = dt.Rows[i]["diemthangtruoc"].ToString();
                    }
                    row[4] = dt.Rows[i]["diem"].ToString();
                    if (dt.Rows[i]["pheduyet"].ToString() == "True")
                    {
                        if (dt.Rows[i]["thang"].ToString() == "01/2013")
                        {
                            row[3] = 0;
                            row[5] = row[4];
                        }
                        else
                        {
                            //row[5] = dt.Rows[i]["diemthangtruoc"].ToString();
                            row[5] = row[3];
                            row[3] = Convert.ToDecimal(row[5].ToString()) - Convert.ToDecimal(row[4].ToString());
                        }
                    }
                    else
                    {
                        row[5] = Convert.ToDecimal(row[3].ToString()) + Convert.ToDecimal(row[4].ToString());
                    }

                    if (dt.Rows[i]["pheduyet"].ToString() == "False")
                    {
                        row[6] = false;
                    }
                    else
                    {
                        row[6] = true;
                    }
                    row[7] = dt.Rows[i]["dienthoai1"].ToString();
                    string ngaysinh = dt.Rows[i]["ngaysinh"].ToString().Substring(0, 2) + "/" + dt.Rows[i]["ngaysinh"].ToString().Substring(3, 2) + "/" + dt.Rows[i]["ngaysinh"].ToString().Substring(6, 4);
                    row[8] = ngaysinh;
                    row[9] = dt.Rows[i]["CMND"].ToString();
                    dskh.Rows.Add(row);
                    //}
                }
            }
            dgvDanhsach.DataSource              = dskh;
            dgvDanhsach.Columns[0].ReadOnly     = true;
            dgvDanhsach.Columns[1].ReadOnly     = true;
            dgvDanhsach.Columns[2].ReadOnly     = true;
            dgvDanhsach.Columns[3].ReadOnly     = true;
            dgvDanhsach.Columns[4].ReadOnly     = true;
            dgvDanhsach.Columns[5].ReadOnly     = true;
            dgvDanhsach.Columns[6].ReadOnly     = false;
            dgvDanhsach.Columns[7].Visible      = false;
            dgvDanhsach.Columns[8].Visible      = true;
            dgvDanhsach.Columns[9].Visible      = false;
            dgvDanhsach.Columns[0].FillWeight   = 30;
            dgvDanhsach.Columns[0].Width        = 20;
            dgvDanhsach.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            dgvDanhsach.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
        }
Beispiel #41
0
        private void btnAdd_Click(object sender, EventArgs e)
        {
            //Проверка на оба текстбокса
            if (cbName.Text == "" || tbCount.Text == "")
            {
                MessageBox.Show("Не все поля заполнены");
                return;
            }
            //Проверка на число
            try
            {
                Int32.Parse(tbCount.Text);
            }
            catch (FormatException)
            {
                MessageBox.Show("Введено неверное количество");
                return;
            }
            //Проверка на значение 0 и меньше
            if (Int32.Parse(tbCount.Text) <= 0)
            {
                MessageBox.Show("Введено неверное количество");
                return;
            }

            MySqlDataAdapter adapter1 = new MySqlDataAdapter(
                "SELECT artikul_lekarstva, price, kolvo " +
                "FROM lekarstva " +
                $"WHERE artikul_lekarstva = {table.Rows[cbName.SelectedIndex][0].ToString()}",
                conn
                );

            adapter1.Fill(table1);

            //Проверка на лекартсво
            if (table1.Rows.Count == 0)
            {
                MessageBox.Show("Товар не найден");
                return;
            }
            bool isLekAdd = false;

            //Поиск лекарства в списке покупок
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                //Если лекарство добавлено
                if (dataGridView1.Rows[i].Cells[0].Value.ToString() == table.Rows[cbName.SelectedIndex][0].ToString())
                {
                    //Проверка на количество
                    int kol = Int32.Parse(dataGridView1.Rows[i].Cells[2].Value.ToString()) + Int32.Parse(tbCount.Text); //Количество товара в списке + добавляемого
                    if (kol > Int32.Parse(table1.Rows[0][2].ToString()))
                    {
                        MessageBox.Show($"Невозможно добавить, товара осталось: {Int32.Parse(table1.Rows[0][2].ToString()) - Int32.Parse(dataGridView1.Rows[i].Cells[2].Value.ToString())}");
                        table1.Clear();
                        return;
                    }
                    dataGridView1.Rows[i].Cells[2].Value = kol;
                    sum        = sum + (Int32.Parse(dataGridView1.Rows[i].Cells[3].Value.ToString()) * Int32.Parse(tbCount.Text));
                    lSumm.Text = Convert.ToString(sum) + " руб";
                    isLekAdd   = true;
                    break;
                }
            }
            //Если лекарство не было добавлено в список
            if (isLekAdd == false)
            {
                //Проверка на количество
                if (Int32.Parse(table1.Rows[0][2].ToString()) < Int32.Parse(tbCount.Text))
                {
                    MessageBox.Show($"Невозможно добавить, товара осталось: {Int32.Parse(table1.Rows[0][2].ToString())}");
                    table1.Clear();
                    return;
                }

                MySqlDataAdapter adapter2 = new MySqlDataAdapter(
                    "SELECT artikul_lekarstva, naimenovanie_lekarstva, price " +
                    "FROM lekarstva " +
                    $"WHERE artikul_lekarstva = {table.Rows[cbName.SelectedIndex][0].ToString()}",
                    conn
                    );
                adapter2.Fill(table2);
                this.dataGridView1.Rows.Add(table2.Rows[0][0].ToString(), table2.Rows[0][1].ToString(), tbCount.Text, table2.Rows[0][2]);
                sum        = sum + (Int32.Parse(table2.Rows[0][2].ToString()) * Int32.Parse(tbCount.Text));
                lSumm.Text = Convert.ToString(sum) + " руб";
                table2.Clear();
            }
            table1.Clear();
        }
Beispiel #42
0
        private void BtnSubmit_Click(object sender, EventArgs e)
        {
            dt.Clear();
            lbSalesText.Items.Clear();

            Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;

            //結束箱號暫存
            int tmpCoNumEnd      = 0;
            int orderNumberCount = gvOrderInput.RowCount - 1;

            if (orderNumberCount == 0)
            {
                MessageBox.Show("請輸入訂單資料!", "錯誤");
            }
            for (int k = 1; k <= orderNumberCount; k++)
            {
                //輸入參數
                String paraOrderNumber  = gvOrderInput.Rows[k - 1].Cells[0].Value.ToString().Trim();
                String paraDeliveryDate = null;
                if (gvOrderInput.Rows[k - 1].Cells[1].Value != null)
                {
                    paraDeliveryDate = gvOrderInput.Rows[k - 1].Cells[1].Value.ToString().Trim();
                }

                var rfcGetPackingFM = rfcRepo.CreateFunction(getPackingData);

                //設置輸入參數
                rfcGetPackingFM.SetValue("P_VBELN", paraOrderNumber);
                if (!string.IsNullOrEmpty(paraDeliveryDate))
                {
                    rfcGetPackingFM.SetValue("P_EDATU", paraDeliveryDate);
                }

                //調用RFC方法
                rfcGetPackingFM.Invoke(rfcDest);

                //輸出參數
                string messageType   = rfcGetPackingFM.GetValue("STYPE").ToString();
                string messageStatus = rfcGetPackingFM.GetValue("STATUS").ToString();

                string messageBoxTitle = "";
                switch (messageType)
                {
                case "S": messageBoxTitle = "成功"; break;

                case "E": messageBoxTitle = "錯誤"; break;

                case "W": messageBoxTitle = "警告"; break;

                case "I": messageBoxTitle = "資訊"; break;

                case "A": messageBoxTitle = "取消"; break;
                }

                if (messageStatus != "")
                {
                    MessageBox.Show(messageStatus, messageBoxTitle);
                }
                else
                {
                    IRfcTable HEADER = rfcGetPackingFM.GetTable("HEADER");
                    IRfcTable ITEM   = rfcGetPackingFM.GetTable("ITEM");
                    IRfcTable TLINE1 = rfcGetPackingFM.GetTable("TLINE1");
                    IRfcTable TLINE2 = rfcGetPackingFM.GetTable("TLINE2");
                    IRfcTable TLINE3 = rfcGetPackingFM.GetTable("TLINE3");

                    //当前内表的索引行
                    HEADER.CurrentIndex = 0;
                    //訂單號碼
                    string orderNumber = HEADER.GetString("VBELN").TrimStart('0');
                    //買方代號
                    string buyerNum = HEADER.GetString("KUNNR_S");
                    //收貨方代號
                    string shiperNum = HEADER.GetString("KUNNR_H");
                    //買方名稱
                    string buyerName = HEADER.GetString("NAME1_S");
                    //收貨方名稱
                    string shiperName = HEADER.GetString("NAME1_H");
                    //預計出貨日
                    string VDATU = Convert.ToDateTime(HEADER.GetString("VDATU")).ToString("yyyy/MM/dd");

                    //累加訂單
                    if (k == 1 && orderNumber != "")
                    {
                        lblOrderNum.Text = "訂單號碼 : " + orderNumber;
                    }
                    else if (orderNumber != "")
                    {
                        lblOrderNum.Text = lblOrderNum.Text + " ; " + orderNumber;
                    }
                    //顯示最後一筆的資料
                    if (k == orderNumberCount)
                    {
                        lblOrderNum.Visible        = true;
                        lblCusNum.Text             = "買方/出貨方 : " + buyerNum + " / " + shiperNum;
                        lblCusNum.Visible          = true;
                        lblCusName.Text            = "買方/出貨方 : " + buyerName + " / " + shiperName;
                        lblCusName.Visible         = true;
                        lblEstDeliveryDate.Visible = true;
                        if (!string.IsNullOrEmpty(paraDeliveryDate))
                        {
                            lblEstDeliveryDate.Text = "第一交貨日 : " + paraDeliveryDate;
                        }
                        else
                        {
                            lblEstDeliveryDate.Text = "第一交貨日未指定";
                        }

                        //銷售內文
                        addTLINEtoListbox(TLINE1);
                        addTLINEtoListbox(TLINE2);
                        addTLINEtoListbox(TLINE3);
                    }

                    for (int i = 0; i <= ITEM.RowCount - 1; i++)
                    {
                        DataRow dr            = dt.NewRow();
                        bool    isHeaderExist = dt.Columns.Contains("箱數");

                        if (i == 0 && !isHeaderExist)
                        {
                            dt.Columns.Add("箱號");
                            dt.Columns.Add("箱數");
                            dt.Columns.Add("客戶物料");
                            dt.Columns.Add("品號");
                            dt.Columns.Add("舊料號");
                            dt.Columns.Add("品名");
                            dt.Columns.Add("總數量");
                            dt.Columns.Add("單位");
                            dt.Columns.Add("總淨重");
                            dt.Columns.Add("總毛重");
                            dt.Columns.Add("總才數");
                            dt.Columns.Add("內盒");
                            dt.Columns.Add("內盒舊品號");
                            dt.Columns.Add("外箱");
                            dt.Columns.Add("外箱舊品號");
                            dt.Columns.Add("客戶訂單");
                            dt.Columns.Add("訂單號碼");
                            dt.Columns.Add("項次");

                            // 以下資料不會轉出成 excel
                            dt.Columns.Add("預計出貨日");
                            dt.Columns.Add("起訖箱號");
                            dt.Columns.Add("單箱數量");
                            dt.Columns.Add("淨重");
                            dt.Columns.Add("毛重");
                            dt.Columns.Add("才數");
                            dt.Columns.Add("包裝指示碼");
                            dt.Columns.Add("滿箱數");
                            dt.Columns.Add("買方代號");
                            dt.Columns.Add("買方名稱");
                            dt.Columns.Add("出貨人代號");
                            dt.Columns.Add("出貨人名稱");
                            dt.Columns.Add("結帳月份");
                            dt.Columns.Add("起始箱號");
                            dt.Columns.Add("結束箱號");
                            dt.Columns.Add("單價");
                            dt.Columns.Add("KEY");
                            dt.Columns.Add("USERID");
                            dt.Columns.Add("原始單價");
                            dt.Columns.Add("客戶折價");
                        }

                        //index
                        HEADER.CurrentIndex = 0;
                        ITEM.CurrentIndex   = i;
                        //箱數
                        int ctnQty = Convert.ToInt32(ITEM.GetString("CTNQTY").ToString().TrimEnd('0').TrimEnd('.'));
                        //起始箱號,結束箱號
                        int ctnNumStart, ctnNumEnd;

                        //箱數計算
                        if (i == 0 && k == 1)
                        {
                            ctnNumStart = 1;
                            ctnNumEnd   = ctnQty;
                            tmpCoNumEnd = ctnNumEnd;
                        }
                        else
                        {
                            ctnNumStart = tmpCoNumEnd + 1;
                            ctnNumEnd   = ctnNumStart + ctnQty - 1;
                            tmpCoNumEnd = ctnNumEnd;
                        }
                        //參考號碼(客戶物料)
                        string cusMat = ITEM.GetString("KDMAT").ToString();
                        //客戶採購單
                        string cusPoNum = ITEM.GetString("BSTKD").ToString();
                        //品號
                        string matNum = ITEM.GetString("MATNR").ToString().TrimStart('0');
                        //品名
                        string matName = ITEM.GetString("ARKTX").ToString();
                        //單位
                        string unitOfMeasure = ITEM.GetString("VRKME").ToString();
                        //數量
                        string totQty = ITEM.GetString("KWMENG").ToString().TrimEnd('0').TrimEnd('.');
                        //內盒
                        string boxMatNum = ITEM.GetString("BOX").ToString().TrimStart('0');
                        //外箱
                        string ctnMatNum = ITEM.GetString("CTN").ToString().TrimStart('0');
                        //滿箱數
                        string fullPackQty = ITEM.GetString("PACKQTY").ToString().TrimEnd('0').TrimEnd('.');
                        //單箱數量
                        string boxQty = ITEM.GetString("BOXQTY").ToString().TrimEnd('0').TrimEnd('.');
                        //淨重
                        string netWeight = ITEM.GetString("NTGEW1").ToString();
                        //總淨重
                        string totNetWeight = ITEM.GetString("NTGEW2").ToString();
                        //毛重
                        string grossWeight = ITEM.GetString("NTGEW3").ToString();
                        //總毛重
                        string totGrossWeight = ITEM.GetString("NTGEW4").ToString();
                        //才數
                        string volume = ITEM.GetString("VOLUM1").ToString();
                        //總才數
                        string totVolume = ITEM.GetString("VOLUM2").ToString();
                        //項次
                        string itemNum = ITEM.GetString("POSNR").ToString().TrimStart('0');
                        //舊料號
                        string oldMatNum = ITEM.GetString("IHREZ_E").ToString();
                        //舊外箱
                        string ctnOldMatNum = ITEM.GetString("CTN_O").ToString();
                        //舊內盒
                        string boxOldMatNum = ITEM.GetString("BOX_O").ToString();
                        //包裝指示碼
                        string packingInstruc = ITEM.GetString("POBJID").ToString();
                        //單價
                        string unitPrice = ITEM.GetString("U_PRICE").ToString();

                        dr["箱數"]    = ctnQty;
                        dr["客戶物料"]  = cusMat;
                        dr["品號"]    = matNum;
                        dr["品名"]    = matName;
                        dr["單箱數量"]  = boxQty.Trim();
                        dr["單位"]    = unitOfMeasure;
                        dr["淨重"]    = netWeight;
                        dr["毛重"]    = grossWeight;
                        dr["才數"]    = volume;
                        dr["內盒"]    = boxMatNum;
                        dr["外箱"]    = ctnMatNum;
                        dr["訂單號碼"]  = orderNumber;
                        dr["舊料號"]   = oldMatNum;
                        dr["客戶訂單"]  = cusPoNum;
                        dr["總數量"]   = totQty;
                        dr["總淨重"]   = totNetWeight;
                        dr["總毛重"]   = totGrossWeight;
                        dr["總才數"]   = totVolume;
                        dr["內盒舊品號"] = boxOldMatNum;
                        dr["外箱舊品號"] = ctnOldMatNum;
                        dr["項次"]    = itemNum;
                        dr["起訖箱號"]  = ctnNumStart + "~" + ctnNumEnd;

                        dr["箱號"]     = ctnNumEnd;
                        dr["包裝指示碼"]  = packingInstruc;
                        dr["滿箱數"]    = fullPackQty;
                        dr["買方代號"]   = buyerNum;
                        dr["買方名稱"]   = buyerName;
                        dr["預計出貨日"]  = Convert.ToDateTime(ITEM.GetString("EDATU")).ToString("yyyyMMdd");
                        dr["出貨人代號"]  = shiperNum;
                        dr["出貨人名稱"]  = shiperName;
                        dr["結帳月份"]   = Convert.ToDateTime(ITEM.GetString("EDATU")).ToString("yyyyMM");
                        dr["起始箱號"]   = ctnNumStart;
                        dr["結束箱號"]   = ctnNumEnd;
                        dr["單價"]     = unitPrice;
                        dr["KEY"]    = packingKey;
                        dr["USERID"] = userName;
                        dr["原始單價"]   = ITEM.GetString("KBTER1").ToString();
                        dr["客戶折價"]   = ITEM.GetString("KBTER2").ToString();
                        dt.Rows.Add(dr);
                    }
                }

                dgvPacking.DataSource = dt.DefaultView;
                dgvPacking.ReadOnly   = true;
                Cursor.Current        = Cursors.Default;
            }
            //datatable加總
            int    sumTotQty         = 0;
            int    sumTotCtnQty      = 0;
            double sumTotNetWeight   = 0.000;
            double sumTotGrossWeight = 0.000;
            double sumTotVolume      = 0.000;

            foreach (DataRow dr in dt.Rows)
            {
                if (dr.RowState != DataRowState.Deleted)
                {
                    sumTotQty += Convert.ToInt32(dr["總數量"]);
                }
                sumTotCtnQty      += Convert.ToInt32(dr["箱數"]);
                sumTotNetWeight   += Convert.ToDouble(dr["總淨重"]);
                sumTotGrossWeight += Convert.ToDouble(dr["總毛重"]);
                sumTotVolume      += Convert.ToDouble(dr["總才數"]);
            }

            lbSalesText.Items.Add("加總數量:" + sumTotQty);
            lbSalesText.Items.Add("加總箱數:" + sumTotCtnQty);
            lbSalesText.Items.Add("加總淨重:" + sumTotNetWeight.ToString("0.000")); // 小數會自動補0,格式為 1.000
            lbSalesText.Items.Add("加總毛重:" + sumTotGrossWeight.ToString("0.000"));
            lbSalesText.Items.Add("加總才數:" + sumTotVolume.ToString("0.000"));
        }
Beispiel #43
0
    public void GetItemData()
    {
        try
        {
            SqlConnection  conn = new SqlConnection(ConfigurationManager.AppSettings["ReportsConnectionString"].ToString());
            SqlDataAdapter adp;
            SqlCommand     Cmd = new SqlCommand();

            if (!strBranch.Contains(","))
            {
                Cmd.CommandTimeout = 0;
                Cmd.CommandType    = CommandType.StoredProcedure;
                Cmd.Connection     = conn;
                conn.Open();
                Cmd.CommandText = "PFC_RPT_SP_BranchItemShippingSalesAnalysis";
                Cmd.Parameters.Add(new SqlParameter("@PeriodMonth", strMonth));
                Cmd.Parameters.Add(new SqlParameter("@PeriodYear", strYear));
                Cmd.Parameters.Add(new SqlParameter("@Branch", strBranch));
                Cmd.Parameters.Add(new SqlParameter("@ShippingAgent", strAgent));
                Cmd.Parameters.Add(new SqlParameter("@CatFrom", strCatFrom));
                Cmd.Parameters.Add(new SqlParameter("@CatTo", strCatTo));
                Cmd.Parameters.Add(new SqlParameter("@VarianceFrom", strVarianceFrom));
                Cmd.Parameters.Add(new SqlParameter("@VarianceTo", strVarianceTo));
                Cmd.Parameters.Add(new SqlParameter("@SalesRep", strSalesRep.Replace("'", "''")));
                adp = new SqlDataAdapter(Cmd);
                adp.Fill(dsItemInfo);
            }
            else
            {
                Cmd.CommandTimeout = 0;
                Cmd.CommandType    = CommandType.StoredProcedure;
                Cmd.Connection     = conn;
                conn.Open();
                Cmd.CommandText = "PFC_RPT_SP_BranchItemShippingSalesAnalysis_all";
                Cmd.Parameters.Add(new SqlParameter("@PeriodMonth", strMonth));
                Cmd.Parameters.Add(new SqlParameter("@PeriodYear", strYear));
                Cmd.Parameters.Add(new SqlParameter("@SBranch", strBranch));

                Cmd.Parameters.Add(new SqlParameter("@NSBranch", Session["UnAuthorizedBranch"].ToString()));
                Cmd.Parameters.Add(new SqlParameter("@TSBranch", Session["AuthorizedBranchTotal"].ToString()));
                Cmd.Parameters.Add(new SqlParameter("@TNSBranch", Session["UnAuthorizedBranchTotal"].ToString()));
                Cmd.Parameters.Add(new SqlParameter("@ShippingAgent", strAgent));
                Cmd.Parameters.Add(new SqlParameter("@CatFrom", strCatFrom));
                Cmd.Parameters.Add(new SqlParameter("@CatTo", strCatTo));
                Cmd.Parameters.Add(new SqlParameter("@VarianceFrom", strVarianceFrom));
                Cmd.Parameters.Add(new SqlParameter("@VarianceTo", strVarianceTo));
                Cmd.Parameters.Add(new SqlParameter("@SalesRep", strSalesRep.Replace("'", "''")));
                adp = new SqlDataAdapter(Cmd);
                adp.Fill(dsItemInfo);
            }

            dt.Clear();
            dt = dsItemInfo.Tables[0];
            dt.DefaultView.Sort   = "Item" + " " + "asc";
            Session["BranchItem"] = dt;

            // Footer table
            if (dsItemInfo.Tables[1] != null)
            {
                Session["dtGrandTotal"] = dsItemInfo.Tables[1];
            }

            ViewState["SortField"] = "Item";
            ViewState["SortMode"]  = "asc";
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message.ToString());
        }
    }
        public static DataTable CA(string docnumber)
        {
            DataTable dtTable = new DataTable();

            dtTable.TableName = "Table";

            SqlConnection cn = new SqlConnection(GlobalClass.SQLConnString());

            System.Data.DataTable dt     = new System.Data.DataTable();
            SqlCommand            cmd    = null;
            SqlDataReader         reader = null;
            SqlDataAdapter        adp;

            cn.Open();

            if (dtTable.Columns.Count == 0)
            {
                //Columns for AspxGridview
                dtTable.Columns.Add("PK", typeof(string));
                dtTable.Columns.Add("OperatingUnit", typeof(string));
                dtTable.Columns.Add("Expense", typeof(string));
                dtTable.Columns.Add("ProcurementCategory", typeof(string));
                dtTable.Columns.Add("Descripiton", typeof(string));
                dtTable.Columns.Add("UOM", typeof(string));
                dtTable.Columns.Add("Qty", typeof(string));
                dtTable.Columns.Add("Cost", typeof(string));
                dtTable.Columns.Add("TotalCost", typeof(string));

                dtTable.Columns.Add("RecQty", typeof(string));
                dtTable.Columns.Add("RecCost", typeof(string));
                dtTable.Columns.Add("RecTotalCost", typeof(string));
            }

            string farm_query = "[dbo].[CAPEXPreview]";

            cmd             = new SqlCommand(farm_query, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@headerdocnum", docnumber);
            cmd.Parameters.AddWithValue("@entity", entitycode);
            //cmd.ExecuteNonQuery();

            adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    DataRow dtRow = dtTable.NewRow();
                    dtRow["PK"]                  = row["PK"].ToString();
                    dtRow["OperatingUnit"]       = row["OprUnit"].ToString();
                    dtRow["ProcurementCategory"] = row["ProdCat"].ToString();
                    dtRow["Descripiton"]         = row["Description"].ToString();
                    dtRow["UOM"]                 = row["UOM"].ToString();
                    dtRow["Qty"]                 = Convert.ToDouble(row["Qty"].ToString()).ToString("N");
                    dtRow["Cost"]                = Convert.ToDouble(row["Cost"].ToString()).ToString("N");
                    dtRow["TotalCost"]           = Convert.ToDouble(row["TotalCost"].ToString()).ToString("N");

                    dtRow["RecQty"]       = Convert.ToDouble(row["EdittedQty"].ToString()).ToString("N");
                    dtRow["RecCost"]      = Convert.ToDouble(row["EdittedCost"].ToString()).ToString("N");
                    dtRow["RecTotalCost"] = Convert.ToDouble(row["EdittiedTotalCost"].ToString()).ToString("N");
                    dtTable.Rows.Add(dtRow);
                }
            }
            dt.Clear();
            cn.Close();
            return(dtTable);
        }
        //Función para llenar el textBox
        private void llenarTextbox(int iBandera)
        {
            try
            {
                texto           = "";
                txtInforme.Text = texto;
                double dbTotal         = 0;
                double dbTotalCantidad = 0;
                dgvInforme.Rows.Clear();

                if (iBandera == 0)
                {
                    sSql = "select rtrim(P.apellidos) + ' '+ rtrim(P.nombres) Nombres , F.id_persona, sum(F.valor) 'Total de Consumos', " +
                           "count(F.id_persona) 'Numero De Consumos' , isnull(F.telefono_factura,' ') Telefono  " +
                           "from  cv403_facturas F inner join tp_personas P " +
                           "on F.id_persona = P.id_persona " +
                           "where F.fecha_factura between '" + sFechaDesde + "' and '" + sFechaHasta + "' " +
                           "group by F.id_persona, F.telefono_factura, P.nombres, P.apellidos";
                }
                else
                {
                    sSql = "select rtrim(P.apellidos) + ' '+ rtrim(P.nombres) Nombres , F.id_persona, sum(F.valor) 'Total de Consumos', " +
                           "count(F.id_persona) 'Numero De Consumos' , isnull(F.telefono_factura,' ') Telefono  " +
                           "from  cv403_facturas F inner join tp_personas P " +
                           "on F.id_persona = P.id_persona " +
                           "where F.fecha_factura between '" + sFechaDesde + "' and '" + sFechaHasta + "' " +
                           " and P.apellidos like '%" + txtBusqueda.Text + "%'  " +
                           "group by F.id_persona, F.telefono_factura, P.nombres, P.apellidos";
                }


                dtConsulta = new System.Data.DataTable();
                dtConsulta.Clear();
                bRespuesta = conexion.GFun_Lo_Busca_Registro(dtConsulta, sSql);

                if (bRespuesta == true)
                {
                    if (dtConsulta.Rows.Count > 0)
                    {
                        texto += "\r\n";
                        texto += "REPORTE DE VENTAS POR CLIENTE ".PadLeft(78, ' ') + "\r\n";
                        texto += ("DESDE " + sFechaDesde + " A " + sFechaHasta).PadLeft(77, ' ') + "\r\n";
                        texto += "\r\n";
                        texto += "NOMBRES".PadRight(35, ' ') + "|# DE TELEFONO".PadLeft(19, ' ') +
                                 "   |TOTAL DE CONSUMO".PadLeft(17, ' ') + "  |CANT. CONSUMO".PadLeft(19, ' ') + "  |PROMEDIO DE CONSUMO|".PadLeft(20, ' ') + "\r\n";
                        texto += "-".PadRight(115, '-') + "\r\n";
                        dgvInforme.Rows.Add();

                        for (int i = 0; i < dtConsulta.Rows.Count; i++)
                        {
                            string sNombre           = dtConsulta.Rows[i].ItemArray[0].ToString();
                            string sTelefono         = dtConsulta.Rows[i].ItemArray[4].ToString();
                            double dbTotalConsumos   = Convert.ToDouble(dtConsulta.Rows[i].ItemArray[2].ToString());
                            double dbCantidadConsumo = Convert.ToDouble(dtConsulta.Rows[i].ItemArray[3].ToString());
                            double dbPromedioConsumo = dbTotalConsumos / dbCantidadConsumo;
                            dbTotal         += Convert.ToDouble(dtConsulta.Rows[i].ItemArray[2].ToString());
                            dbTotalCantidad += Convert.ToDouble(dtConsulta.Rows[i].ItemArray[3].ToString());

                            if (sNombre.Length > 35)
                            {
                                sNombre = sNombre.Substring(0, 35);
                            }
                            texto += sNombre.PadRight(35, ' ') + sTelefono.PadLeft(19, ' ') + dbTotalConsumos.ToString("N2").PadLeft(17, ' ') +
                                     dbCantidadConsumo.ToString("N2").PadLeft(19, ' ') + dbPromedioConsumo.ToString("N2").PadLeft(20) + "\r\n";
                            dgvInforme.Rows.Add(sNombre, sTelefono, dbTotalConsumos.ToString("N2"), dbCantidadConsumo.ToString("N2"), dbPromedioConsumo.ToString("N2"));
                        }

                        texto += "\r\n";
                        texto += "\r\n";
                        dgvInforme.Rows.Add();
                        dgvInforme.Rows.Add();
                        dgvInforme.Rows.Add();
                        texto += "-".PadRight(115, '-') + "\r\n";
                        texto += "TOTALES".PadRight(54, ' ') + ("$" + dbTotal.ToString("N2")).PadLeft(17, ' ') + dbTotalCantidad.ToString("N2").PadLeft(19, ' ');
                        dgvInforme.Rows.Add("TOTALES", "", "$" + dbTotal, "$" + dbTotalCantidad);
                        txtInforme.Text = texto;
                    }
                    else
                    {
                        if (iBandera == 0)
                        {
                            ok.LblMensaje.Text = "No hay datos para ser mostrados en el rango de fechas selecionadas";
                            ok.ShowInTaskbar   = false;
                            ok.ShowDialog();
                        }
                    }
                }
                else
                {
                    ok.LblMensaje.Text = "Ocurrió un porblema al conectarse a la base de datos";
                    ok.ShowInTaskbar   = false;
                    ok.ShowDialog();
                }
            }
            catch (Exception ex)
            {
                catchMensaje.LblMensaje.Text = ex.ToString();
                catchMensaje.ShowInTaskbar   = false;
                catchMensaje.ShowDialog();
            }
        }
Beispiel #46
0
        private void pivotGridControl1_CellClick(object sender, PivotCellEventArgs e)
        {
            //Employee_24_7_Production_user_id = userid.ToString();
            SplashScreenManager.ShowForm(this, typeof(Ordermanagement_01.Masters.WaitForm1), true, true, false);
            try
            {
                PivotGridHitInfo hi = pivotGridControl1.CalcHitInfo(pivotGridControl1.PointToClient(MousePosition));
                if (hi.HitTest == PivotGridHitTest.Cell)
                {
                    string V_Data = "";
                    string Column_Name = "";
                    string Row_Value_Type = ""; string Column_Value_Type = "";
                    string User_Name = ""; string Branch_Name = "";
                    string V_UserName = ""; string V_Date = "";
                    string V_BranchName = "";

                    Row_Value_Type    = hi.CellInfo.RowValueType.ToString();
                    Column_Value_Type = hi.CellInfo.ColumnValueType.ToString();

                    // MessageBox.Show("Row_value_Type"+Row_Value_Type + " Column_Value_Type:" + Column_Value_Type);
                    Column_Name = hi.CellInfo.DataField.FieldName.ToString();
                    //Row_Name = hi.CellInfo.RowField.FieldName.ToString();


                    foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.ColumnArea))
                    {
                        if (Column_Name == "No_Of_Errors")
                        {
                            V_Data = e.GetFieldValue(pivotGridField2).ToString();
                        }
                        if (Column_Name == "No_of_Completed_orders")
                        {
                            V_Data = e.GetFieldValue(pivotGridField3).ToString();
                        }
                        if (Column_Name == "Accuracy")
                        {
                            V_Data = e.GetFieldValue(pivotGridField4).ToString();
                        }
                    }

                    int    month_id = 0; int year_id = 0;
                    object obj        = lookUpEdit_Accuracy_Month.EditValue;
                    string month_name = lookUpEdit_Accuracy_Month.Text;
                    if (obj.ToString() != "0")
                    {
                        month_id = (int)obj;
                    }

                    object obj_year = lookUpEdit_Accuracy_Year.EditValue;
                    string year     = lookUpEdit_Accuracy_Year.Text;
                    if (obj_year.ToString() != "0")
                    {
                        year_id = (int)obj_year;
                    }
                    // get First and last day of the Month and Year

                    first = new DateTime(year_id, month_id, 1);
                    last  = first.AddMonths(1).AddSeconds(-1);

                    string Fromdate = first.ToString();
                    string Todate   = last.ToString();
                    //foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.RowArea))
                    //{

                    //    V_UserName = e.GetFieldValue(pivotGridField5).ToString();
                    //    User_Name = V_UserName;
                    //}
                    //foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.ColumnArea))
                    //{

                    //    V_Date = e.GetFieldValue(pivotGridField1).ToString();

                    //    //Production_Date = V_Date;
                    //}
                    //foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.RowArea))
                    //{

                    //    V_BranchName = e.GetFieldValue(pivotGridField12).ToString();

                    //    Branch_Name = V_BranchName;
                    //}



                    if (V_Data != "" && V_Data != "0")
                    {
                        if (Row_Value_Type == "Value" && Column_Value_Type == "Value")// This is for Non Summary Click Event
                        {
                            foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.ColumnArea))
                            {
                                V_Date = e.GetFieldValue(pivotGridField1).ToString();
                            }
                            foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.RowArea))
                            {
                                V_UserName = e.GetFieldValue(pivotGridField5).ToString();
                                User_Name  = V_UserName;
                            }
                            foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.RowArea))
                            {
                                V_BranchName = e.GetFieldValue(pivotGridField12).ToString();
                                Branch_Name  = V_BranchName;
                            }

                            //if (V_Data != "" && V_Data != "0")
                            //{

                            Hashtable             ht_get_grid = new Hashtable();
                            System.Data.DataTable dt_get_grid = new System.Data.DataTable();
                            ht_get_grid.Clear();
                            dt_get_grid.Clear();
                            ht_get_grid.Add("@Trans", "GET_USER_ID");
                            ht_get_grid.Add("@Emp_Name", User_Name);
                            dt_get_grid = dataaccess.ExecuteSP("Sp_Daily_Status_Top_Efficiency_Calculation", ht_get_grid);

                            //
                            Hashtable             ht_insert = new Hashtable();
                            System.Data.DataTable dt_insert = new System.Data.DataTable();
                            //ht_insert.Clear();
                            //dt_insert.Clear();

                            ht_insert.Add("@Trans", "INSERT_INTO_TEMP_USER_NEW_USER_WISE");
                            ht_insert.Add("@User_Id", dt_get_grid.Rows[0]["User_id"].ToString());
                            ht_insert.Add("@Production_Date", V_Date.ToString());
                            dt_insert = dataaccess.ExecuteSP("Sp_Employee_Production_Score_Board", ht_insert);

                            Ordermanagement_01.Reports.Accuracy_Detail_Section TargeDashboard
                                = new Ordermanagement_01.Reports.Accuracy_Detail_Section(int.Parse(dt_get_grid.Rows[0]["User_id"].ToString()), User_Role_Id, V_Date.ToString(), int.Parse(dt_get_grid.Rows[0]["Branch_ID"].ToString()), "", "");
                            TargeDashboard.Show();

                            //}
                            //else
                            //{
                            //    SplashScreenManager.CloseForm(false);
                            //}
                        }
                        //2

                        else if (Row_Value_Type == "Value" && Column_Value_Type == "Total")// this is For Column Grand Total // this is for Single user & All Date
                        {
                            //if (V_Data != "" && V_Data != "0")
                            //{

                            Hashtable             ht_get_grid = new Hashtable();
                            System.Data.DataTable dt_get_grid = new System.Data.DataTable();
                            ht_get_grid.Clear();
                            dt_get_grid.Clear();
                            ht_get_grid.Add("@Trans", "GET_USER_ID");
                            ht_get_grid.Add("@Emp_Name", User_Name);
                            // ht_get_grid.Add("@Branch_Name", Branch_Name);
                            dt_get_grid = dataaccess.ExecuteSP("Sp_Daily_Status_Top_Efficiency_Calculation", ht_get_grid);

                            //Hashtable htinsert = new Hashtable();
                            //System.Data.DataTable dtinsert = new System.Data.DataTable();

                            //htinsert.Add("@Trans", "INSERT_INTO_TEMP_USER");
                            //htinsert.Add("@Production_Date", V_Date.ToString());
                            //htinsert.Add("@User_Id", int.Parse(dt_get_grid.Rows[0]["User_id"].ToString()));
                            //dtinsert = dataaccess.ExecuteSP("Sp_Employee_Production_Score_Board", htinsert);

                            //Hashtable htinsert = new Hashtable();
                            //System.Data.DataTable dtinsert = new System.Data.DataTable();

                            //htinsert.Add("@Trans", "INSERT_INTO_TEMP_USER_1");
                            //htinsert.Add("@From_Date", Fromdate.ToString());
                            //htinsert.Add("@To_Date", Todate.ToString());
                            //dtinsert = dataaccess.ExecuteSP("Sp_Employee_Production_Score_Board", htinsert);


                            Ordermanagement_01.Reports.Accuracy_Detail_Section TargeDashboard
                                = new Ordermanagement_01.Reports.Accuracy_Detail_Section(int.Parse(dt_get_grid.Rows[0]["User_id"].ToString()), User_Role_Id, V_Date.ToString(), int.Parse(dt_get_grid.Rows[0]["Branch_ID"].ToString()), "", "");
                            TargeDashboard.Show();

                            //}
                            //else
                            //{
                            //    SplashScreenManager.CloseForm(false);
                            //}
                        }
                        //3
                        else if (Row_Value_Type == "Value" && Column_Value_Type == "GrandTotal")// this is For Column Grand Total // this is for Single user & All Date
                        {
                            foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.RowArea))
                            {
                                V_UserName = e.GetFieldValue(pivotGridField5).ToString();
                                User_Name  = V_UserName;
                            }


                            //if (V_Data != "" && V_Data != "0")
                            //{
                            Hashtable             ht_get_grid = new Hashtable();
                            System.Data.DataTable dt_get_grid = new System.Data.DataTable();
                            ht_get_grid.Clear();
                            dt_get_grid.Clear();
                            ht_get_grid.Add("@Trans", "GET_USER_ID");
                            ht_get_grid.Add("@Emp_Name", User_Name);
                            // ht_get_grid.Add("@Branch_Name", Branch_Name);
                            dt_get_grid = dataaccess.ExecuteSP("Sp_Daily_Status_Top_Efficiency_Calculation", ht_get_grid);

                            //Hashtable htinsert = new Hashtable();
                            //System.Data.DataTable dtinsert = new System.Data.DataTable();

                            //htinsert.Add("@Trans", "INSERT_INTO_TEMP_USER");
                            //htinsert.Add("@Production_Date", V_Date.ToString());
                            //htinsert.Add("@User_Id", int.Parse(dt_get_grid.Rows[0]["User_id"].ToString()));
                            //dtinsert = dataaccess.ExecuteSP("Sp_Employee_Production_Score_Board", htinsert);


                            Ordermanagement_01.Reports.Accuracy_Detail_Section TargeDashboard
                                = new Ordermanagement_01.Reports.Accuracy_Detail_Section(int.Parse(dt_get_grid.Rows[0]["User_id"].ToString()), User_Role_Id, V_Date.ToString(), int.Parse(dt_get_grid.Rows[0]["Branch_ID"].ToString()), Fromdate, Todate);
                            TargeDashboard.Show();
                            //}
                            //else
                            //{
                            //    SplashScreenManager.CloseForm(false);
                            //}
                        }
                        //4
                        else if (Row_Value_Type == "Total" && Column_Value_Type == "Value")// this is For Row Total // this is for Single user & All Date
                        {
                            foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.ColumnArea))
                            {
                                V_Date = e.GetFieldValue(pivotGridField1).ToString().Trim();
                            }
                            foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.RowArea))
                            {
                                string val2 = e.GetFieldValue(pivotGridField12).ToString();
                                Branch_Name = val2;
                            }

                            //if (V_Data != "" && V_Data != "0")
                            //{
                            Hashtable             ht_get_grid = new Hashtable();
                            System.Data.DataTable dt_get_grid = new System.Data.DataTable();
                            ht_get_grid.Clear();
                            dt_get_grid.Clear();
                            ht_get_grid.Add("@Trans", "GET_BRANCH_ID");
                            ht_get_grid.Add("@Branch_Name", Branch_Name);
                            dt_get_grid = dataaccess.ExecuteSP("Sp_Daily_Status_Top_Efficiency_Calculation", ht_get_grid);

                            Ordermanagement_01.Reports.Accuracy_Detail_Section TargeDashboard = new Ordermanagement_01.Reports.Accuracy_Detail_Section(0, User_Role_Id, V_Date.ToString(), int.Parse(dt_get_grid.Rows[0]["Branch_ID"].ToString()), "", "");
                            TargeDashboard.Show();
                            //}
                            //else
                            //{
                            //    SplashScreenManager.CloseForm(false);
                            //}
                        }

                        //5
                        else if (Row_Value_Type == "GrandTotal" && Column_Value_Type == "Value")// this is For row Grand Total // this is for Single date & All Date
                        {
                            foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.ColumnArea))
                            {
                                V_Date = e.GetFieldValue(pivotGridField1).ToString().Trim();
                            }

                            //if (V_Data != "" && V_Data != "0")
                            //{

                            Ordermanagement_01.Reports.Accuracy_Detail_Section TargeDashboard = new Ordermanagement_01.Reports.Accuracy_Detail_Section(0, User_Role_Id, V_Date.ToString(), 0, "", "");
                            TargeDashboard.Show();
                            //}
                            //else
                            //{
                            //    SplashScreenManager.CloseForm(false);
                            //}
                        }
                        //6
                        else if (Row_Value_Type == "Total" && Column_Value_Type == "GrandTotal")// this is For Row Total // this is for Single user & All Date
                        {
                            foreach (var field in pivotGridControl1.GetFieldsByArea(PivotArea.RowArea))
                            {
                                string val2 = e.GetFieldValue(pivotGridField12).ToString();
                                Branch_Name = val2;
                            }

                            //if (V_Data != "" && V_Data != "0")
                            //{

                            Hashtable             ht_get_grid = new Hashtable();
                            System.Data.DataTable dt_get_grid = new System.Data.DataTable();
                            ht_get_grid.Clear();
                            dt_get_grid.Clear();
                            ht_get_grid.Add("@Trans", "GET_BRANCH_ID");
                            ht_get_grid.Add("@Branch_Name", Branch_Name);
                            dt_get_grid = dataaccess.ExecuteSP("Sp_Daily_Status_Top_Efficiency_Calculation", ht_get_grid);


                            Ordermanagement_01.Reports.Accuracy_Detail_Section TargeDashboard = new Ordermanagement_01.Reports.Accuracy_Detail_Section(0, User_Role_Id, V_Date.ToString(), int.Parse(dt_get_grid.Rows[0]["Branch_ID"].ToString()), Fromdate, Todate);
                            TargeDashboard.Show();
                            //}
                            //else
                            //{
                            //       SplashScreenManager.CloseForm(false);
                            //}
                        }

                        //7
                        else if (Row_Value_Type == "GrandTotal" && Column_Value_Type == "GrandTotal")// this is For Row Total // this is for Single user & All Date
                        {
                            //if (V_Data != "" && V_Data != "0")
                            //{

                            Ordermanagement_01.Reports.Accuracy_Detail_Section TargeDashboard = new Ordermanagement_01.Reports.Accuracy_Detail_Section(0, User_Role_Id, "", 0, Fromdate, Todate);
                            TargeDashboard.Show();

                            //}
                            //else
                            //{
                            //    SplashScreenManager.CloseForm(false);
                            //}
                        }
                    }
                    else
                    {
                        SplashScreenManager.CloseForm(false);
                    }
                }
                else
                {
                    SplashScreenManager.CloseForm(false);
                    MessageBox.Show("Error Occured Please Check With Administrator");
                }
            }
            catch (Exception ex)
            {
                //Close Wait Form
                SplashScreenManager.CloseForm(false);
                MessageBox.Show("Error Occured Please Check With Administrator");
            }
            finally
            {
                //Close Wait Form
                SplashScreenManager.CloseForm(false);
            }
        }
        public List <string> ObjTypeChannelsMatched = new List <string>();    //список каналов типа объекта


        public void Big_SQL(List <int> ObjID) //ОНО СРАБОТАЕТ ТАК? //Запрос всех каналов
        {
            // Делаем часть строки с ID для запроса Каналов
            string Ch        = "";
            bool   firstIter = true;

            for (int i = 0; i < ObjID.Count; i++)   // объединили все id в один запрос
            {
                if (firstIter)
                {
                    Ch = Ch + " (CARDPARAMS.CARDID =  " + ObjID[i].ToString() + " and OBJTYPEPARAM.ID = CARDPARAMS.OBJTYPEPARAMID) "; firstIter = false;
                }                                                                                                                                                        // запросы проверены, таким образом мы не получим бесконечного цикла в ответе и ID каналов подменятся на имена
                else
                {
                    Ch = Ch + " or (CARDPARAMS.CARDID = " + ObjID[i].ToString() + " and OBJTYPEPARAM.ID = CARDPARAMS.OBJTYPEPARAMID) ";
                }
            }

            //1. Запрос параметров каналов
            SQL_CARDPARAMS = "Select CARDPARAMS.S0, CARDPARAMS.S100, CARDPARAMS.M, CARDPARAMS.PLC_VARNAME, CARDPARAMS.ED_IZM, CARDPARAMS.ARH_APP, CARDPARAMS.DISC, CARDPARAMS.KA, CARDPARAMS.KB, CARDPARAMS.OBJTYPEPARAMID, OBJTYPEPARAM.NAME, CARDPARAMS.CARDID from CARDPARAMS, OBJTYPEPARAM where " + Ch;
            ProgramConnect connect = new ProgramConnect();

            connect.ConnectToBase(SQL_CARDPARAMS, BaseAddr);
            TmpDG = connect.dt1;

            for (int i = 0; i < TmpDG.Rows.Count; i++)
            {
                var objChnl = new SQL_TeconObjectChannel()
                {
                    S0          = TmpDG.Rows[i][0].ToString(),
                    S100        = TmpDG.Rows[i][1].ToString(),
                    M           = TmpDG.Rows[i][2].ToString(),
                    PLC_VARNAME = TmpDG.Rows[i][3].ToString(),
                    ED_IZM      = TmpDG.Rows[i][4].ToString(),
                    ARH_APP     = TmpDG.Rows[i][5].ToString(),
                    DISC        = TmpDG.Rows[i][6].ToString(),
                    KA          = TmpDG.Rows[i][7].ToString(),
                    KB          = TmpDG.Rows[i][8].ToString(),
                    ID          = TmpDG.Rows[i][9].ToString(),        //OBJPARAMID
                    ChannelName = TmpDG.Rows[i][10].ToString(),
                    LoadID      = Convert.ToInt32(TmpDG.Rows[i][11]), //ID исходный
                };

                //connect.ConnectToBase("Select OBJTYPEPARAM.NAME from OBJTYPEPARAM where OBJTYPEPARAM.ID = " + objChnl.ID, BaseAddr);
                //objChnl.ChannelName = connect.dt1.Rows[0][0].ToString();
                SQL_Channels.Add(objChnl);
            }
            TmpDG.Clear();

            //2. Запрос деф параметров каналов
            //На удивление, имеем аналогичную часть запроса как и в предыдущем шаге, поэтому ничего менять в ней не будем.

            SQL_CARDPARAMS = "select OBJTYPEPARAM.disc, OBJTYPEPARAM.isev, OBJTYPEPARAM.NAME, cardparams.cardid, OBJTYPEPARAM.id  from OBJTYPEPARAM, cardparams where  " + Ch;
            ProgramConnect connect1 = new ProgramConnect();  // не до экспериментов, создал новую переменную, можно проверить потом, прокатит с той же или нет

            connect1.ConnectToBase(SQL_CARDPARAMS, BaseAddr);
            TmpDG = connect1.dt1;

            for (int i = 0; i < TmpDG.Rows.Count; i++)
            {
                var DefobjChnl = new SQL_TeconObjectChannel()
                {
                    S0          = "0",
                    S100        = "100",
                    M           = "1",
                    PLC_VARNAME = "",
                    ED_IZM      = "",
                    ARH_APP     = TmpDG.Rows[i][1].ToString(),
                    DISC        = TmpDG.Rows[i][0].ToString(),
                    KA          = "1",
                    KB          = "0",
                    ID          = TmpDG.Rows[i][4].ToString(),       //OBJPARAMID
                    ChannelName = TmpDG.Rows[i][2].ToString(),
                    LoadID      = Convert.ToInt32(TmpDG.Rows[i][3]), //ID исходный
                };

                //connect.ConnectToBase("Select OBJTYPEPARAM.NAME from OBJTYPEPARAM where OBJTYPEPARAM.ID = " + objChnl.ID, BaseAddr);
                //objChnl.ChannelName = connect.dt1.Rows[0][0].ToString();
                SQL_DefChannels.Add(DefobjChnl);
            }

            TmpDG.Clear();

            //3. Запрос параметров тех объекта
            Ch        = "";
            firstIter = true;
            for (int i = 0; i < ObjID.Count; i++)   // объединили все id в один запрос
            {
                if (firstIter)
                {
                    Ch = Ch + " (CARDS.ID = " + ObjID[i].ToString() + " and CARDS.OBJTYPEID = OBJTYPE.ID and CARDS.EVKLID = EVKLASSIFIKATOR.ID and CARDS.TEMPLATEID = ISAOBJ.ID and CARDS.KLID = KLASSIFIKATOR.ID) "; firstIter = false;
                }                                                                                                                                                                                                                                        // запросы проверены, таким образом мы не получим бесконечного цикла в ответе и ID каналов подменятся на имена
                else
                {
                    Ch = Ch + " or (CARDS.ID = " + ObjID[i].ToString() + " and CARDS.OBJTYPEID = OBJTYPE.ID and CARDS.EVKLID = EVKLASSIFIKATOR.ID and CARDS.TEMPLATEID = ISAOBJ.ID and CARDS.KLID = KLASSIFIKATOR.ID) ";
                }
            }

            SQL_CARDS = "Select CARDS.MARKA, CARDS.NAME, CARDS.DISC, OBJTYPE.NAME, CARDS.ARH_PER, CARDS.OBJSIGN, CARDS.PLC_ID, CARDS.PLC_GR, EVKLASSIFIKATOR.NAME, CARDS.KKS, ISAOBJ.NAME, KLASSIFIKATOR.NAME, CARDS.PLC_VARNAME, CARDS.PLC_ADRESS, CARDS.OBJTYPEID, CARDS.ID from CARDS, OBJTYPE, KLASSIFIKATOR, EVKLASSIFIKATOR, ISAOBJ where " + Ch; //, RESOURCES
            ProgramConnect connect2 = new ProgramConnect();                                                                                                                                                                                                                                                                                             // не до экспериментов, создал новую переменную, можно проверить потом, прокатит с той же или нет

            connect2.ConnectToBase(SQL_CARDS, BaseAddr);
            TmpDG = connect2.dt1;

            //---------------------------Заполняем параметры объекта---------------------------------------------------------------
            for (int i = 0; i < TmpDG.Rows.Count; i++)
            {
                var obj = new SQL_TeconObject()
                {
                    Marka               = TmpDG.Rows[i][0].ToString(),
                    Name                = TmpDG.Rows[i][1].ToString(),
                    Disc                = TmpDG.Rows[i][2].ToString(),
                    ObjTypeName         = TmpDG.Rows[i][3].ToString(),
                    Arc_Per             = TmpDG.Rows[i][4].ToString(),
                    ObjSign             = TmpDG.Rows[i][5].ToString(),
                    PLC_Name            = TmpDG.Rows[i][6].ToString(),
                    PLC_GR              = TmpDG.Rows[i][7].ToString(),
                    EVKLASSIFIKATORNAME = TmpDG.Rows[i][8].ToString(),
                    KKS               = TmpDG.Rows[i][9].ToString(),
                    POUNAME           = TmpDG.Rows[i][10].ToString(),
                    KLASSIFIKATORNAME = TmpDG.Rows[i][11].ToString(),
                    PLC_varname       = TmpDG.Rows[i][12].ToString(),
                    PLC_address       = TmpDG.Rows[i][13].ToString(),
                    ObjTypeID         = Convert.ToInt16(TmpDG.Rows[i][14]),
                    LoadID            = Convert.ToInt16(TmpDG.Rows[i][15])
                };
                //MessageBox.Show(i.ToString());
                SQL_Objects.Add(obj);
            }
            TmpDG.Clear();
            //4. Запрос одного параметра с названием ПЛК(ОТДЕЛЬНО ПОДУМОЙ!)
            //ПОКА БЕЗ ЭТОГО ПРОБУЕМ, ПОТОМ ПРИКРУТИМ
        }
Beispiel #48
0
        private void btn_validate_Click(object sender, EventArgs e)
        {
            if (validate() != false)
            {
                Hashtable htcheck = new Hashtable();
                DataTable dtcheck = new DataTable();

                htcheck.Clear();
                dtcheck.Clear();
                if (Node_Type == "Parent")
                {
                    htcheck.Add("@Trans", "CHECK_PARENT");
                }


                htcheck.Add("@Order_ID", Order_ID);
                htcheck.Add("@Order_Status_Id", Order_Status);
                htcheck.Add("@Task_Confirm_Id", Task_Confirm_Id);
                dtcheck = dataaccess.ExecuteSP("Sp_Order_Task_Confirmation", htcheck);

                if (dtcheck.Rows.Count > 0)
                {
                    Check = int.Parse(dtcheck.Rows[0]["Count"].ToString());
                }

                if (Check == 0)
                {
                    Hashtable hsforSP = new Hashtable();
                    DataTable dt      = new System.Data.DataTable();
                    hsforSP.Clear();
                    dt.Clear();

                    if (Node_Type == "Parent")
                    {
                        hsforSP.Add("@Task_Confirm_Id", Task_Confirm_Id);
                        hsforSP.Add("@Node_Type", "Parent");
                    }
                    else if (Node_Type == "Sub")
                    {
                        hsforSP.Add("@Task_Confirm_Id", Task_Confirm_Id);
                        hsforSP.Add("@Task_Confirm_Sub_Id", Task_Sub_Id);
                        hsforSP.Add("@Node_Type", "Sub");
                    }
                    else if (Node_Type == "Child")
                    {
                        hsforSP.Add("@Task_Confirm_Id", Task_Confirm_Id);
                        hsforSP.Add("@Task_Confirm_Sub_Id", Task_Sub_Id);
                        hsforSP.Add("@Task_Confirm_Child_Id", Task_Child_Id);
                        hsforSP.Add("@Node_Type", "Child");
                    }

                    if (ddl_Option.SelectedIndex > 0)
                    {
                        Option_Id = int.Parse(ddl_Option.SelectedValue.ToString());
                    }
                    else
                    {
                        Option_Id = 0;
                    }
                    if (ddl_Reason.SelectedIndex > 0)
                    {
                        Reason_Id = int.Parse(ddl_Reason.SelectedValue.ToString());
                    }
                    else
                    {
                        Reason_Id = 0;
                    }
                    hsforSP.Add("@Trans", "INSERT");
                    hsforSP.Add("@Order_ID", Order_ID);
                    hsforSP.Add("@Order_Status_Id", Order_Status);

                    hsforSP.Add("@Confirmed", confirmed);

                    hsforSP.Add("@Option_Id", Option_Id);
                    if (Reason_Id != 0)
                    {
                        hsforSP.Add("@Reason_Id", Reason_Id);
                    }
                    else
                    {
                        hsforSP.Add("@Reason", txt_Reason.Text);
                    }
                    hsforSP.Add("@User_Id", userid);
                    hsforSP.Add("@EnteredDate", DateTime.Now);
                    hsforSP.Add("@status", "True");
                    hsforSP.Add("@Inserted_By", userid);
                    hsforSP.Add("@Instered_Date", DateTime.Now);
                    dt = dataaccess.ExecuteSP("Sp_Order_Task_Confirmation", hsforSP);
                    this.Close();
                    //   Check_Parent_Sub_Chld();
                    Populate_SubQuestion();
                }
            }
        }
Beispiel #49
0
        /// <summary>
        /// 合并按钮点击事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            scoreDic.Clear();
            scoreList.Clear();
            tempDataTable.Clear();
            if (dataTables.Count <= 0)
            {
                MessageBox.Show("请先导入要合并的Excel表");
                return;
            }
            else if (dataTables.Count <= 1)
            {
                MessageBox.Show("请导入至少两个表");
                return;
            }
            for (int i = 0; i < dataTables.Count; i++)
            {
                string temp1 = dataTables[i].Rows[0]["F2"].ToString();
                string temp2 = string.Empty;
                if (i + 1 >= dataTables.Count)
                {
                    temp2 = temp1;
                }
                else
                {
                    temp2 = dataTables[i + 1].Rows[0]["F2"].ToString();
                }
                if (comboBox1.Text == comboBox1.Items[0].ToString())
                {
                    if (temp1 != temp2)
                    {
                        MessageBox.Show(string.Format("合并类型有误,请查看表{0},{1}重新选择合并类型", dataTables[i].TableName, dataTables[i + 1].TableName));
                        return;
                    }
                    else
                    {
                        tempDataTable.Merge(dataTables[i], true, MissingSchemaAction.AddWithKey);
                    }
                }
                else if (comboBox1.Text == comboBox1.Items[1].ToString())
                {
                    if (i + 1 >= dataTables.Count)
                    {
                        temp2 = "";
                    }
                    if (temp1 != temp2)
                    {
                        //总成绩合并
                        AddTable(tempDataTable, dataTables[i]);
                    }
                    else
                    {
                        MessageBox.Show(string.Format("合并类型有误,请查看表{0},{1}重新选择合并类型", dataTables[i].TableName, dataTables[i + 1].TableName));
                        return;
                    }
                }
                else
                {
                    MessageBox.Show("合并类型有误,请重新选择");
                }
            }

            foreach (TabPage tempPage in tabCon_Excel.TabPages)
            {
                if (tempPage.Text == "合并表")
                {
                    MessageBox.Show("已存在合并表,不能重复合并");
                    return;
                }
            }
            if (comboBox1.Text == comboBox1.Items[1].ToString())
            {
                string id    = string.Empty;
                string value = string.Empty;
                for (int i = 0; i < tempDataTable.Columns.Count; i++)
                {
                    if (tempDataTable.Rows[0][tempDataTable.Columns[i]].ToString() == "总分")
                    {
                        if (i + 1 >= tempDataTable.Columns.Count)
                        {
                            break;
                        }
                        else
                        {
                            value = tempDataTable.Columns[i + 1].ColumnName.ToString();
                            scoreList.Add(value);
                        }
                    }
                }

                int        index      = tempDataTable.Columns.Count;
                DataColumn dataColumn = new DataColumn();
                dataColumn.ColumnName = "编号";
                tempDataTable.Columns.Add(dataColumn);
                for (int i = 1; i <= scoreList.Count; i++)
                {
                    DataColumn dataColumn1 = new DataColumn();
                    dataColumn1.ColumnName = "比赛项目" + i;
                    tempDataTable.Columns.Add(dataColumn1);
                }
                DataColumn column1 = new DataColumn();
                column1.ColumnName = "比赛总成绩";
                tempDataTable.Columns.Add(column1);
                for (int a = 0; a < tempDataTable.Rows.Count; a++)
                {
                    tempDataTable.Rows[a]["编号"] = tempDataTable.Rows[a][tempDataTable.Columns[0]];
                    double f = 0;
                    for (int b = 0; b < scoreList.Count; b++)
                    {
                        string tempColumnName = "比赛项目" + (b + 1);
                        string temp           = tempDataTable.Rows[a][scoreList[b]].ToString();
                        tempDataTable.Rows[a][tempColumnName] = tempDataTable.Rows[a][scoreList[b]];
                        f += Convert.ToDouble(tempDataTable.Rows[a][tempColumnName]);
                    }
                    tempDataTable.Rows[a]["比赛总成绩"] = f;
                }
            }
            TabPage page = new TabPage();

            page.Name = "Page" + tabCon_Excel.TabPages.Count;
            page.Text = "合并表";
            tabCon_Excel.Controls.Add(page);
            tabCon_Excel.SelectedTab = page;
            DataGridView dataGridView = new DataGridView();

            dataGridView.ReadOnly            = true;
            dataGridView.Parent              = page;
            dataGridView.Size                = page.Size;
            dataGridView.DataSource          = tempDataTable;
            dataGridView.AutoGenerateColumns = false;
        }
        private void BT_Exportar_Click(object sender, EventArgs e)
        {
            MySqlConnection con     = BDConexicon.conectar();
            DateTime        inicio  = DT_inicio.Value;
            DateTime        fin     = DT_fin.Value;
            ArrayList       cajeras = new ArrayList();


            //OBTENER EL NOMBRE DE LAS CAJERAS QUE CAPTURARON ETIQUETAS EN MAL ESTADO
            MySqlCommand    cmd = new MySqlCommand("SELECT  DISTINCT cajera FROM rd_etiquetas WHERE fecha BETWEEN '" + inicio.ToString("yyyy-MM-dd") + "'and '" + fin.ToString("yyyy-MM-dd") + "'", con);
            MySqlDataReader dr  = cmd.ExecuteReader();

            System.Data.DataTable dt = new System.Data.DataTable();
            //LAS CAJERAS SE GUARDAN EN EL ARREGLO CAJERAS
            while (dr.Read())
            {
                cajeras.Add(dr["cajera"].ToString());
            }
            dr.Close();

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Application.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Worksheet hoja = null;//DECLARO UNA HOJA DE EXCEL


            try
            {//RELLENO EL DATAGRID CON LOS DATOS DE LAS ETIQUETAS QUE LAS CAJERAS HAYAN CAPTURADO, RECORRO ESE DATAGRID Y LO EXPORTO A UNA HOJA DE EXCEL
                for (int i = 0; i < cajeras.Count; i++)
                {
                    DG_etiquetas.DataSource = null;                                               //INICIALIZO EN NULL MI DATAGRID
                    dt.Clear();                                                                   //LIMPIO EL DATATABLE
                    hoja      = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets.Add(); //AGREGO LA HOJA DE EXCEL
                    hoja.Name = cajeras[i].ToString();

                    MySqlCommand     cmd2 = new MySqlCommand("SELECT clave as CLAVE,descrip AS DESCRIPCION,depto AS DEPTO,art_ceros AS CEROS,sin_etiqueta AS SIN_ETIQUETA,borrosa AS BORROSA,hora AS HORA,fecha AS FECHA FROM rd_etiquetas WHERE cajera='" + cajeras[i].ToString() + "' and fecha BETWEEN '" + inicio.ToString("yyyy-MM-dd") + "'and '" + fin.ToString("yyyy-MM-dd") + "'", con);
                    MySqlDataAdapter ad   = new MySqlDataAdapter(cmd2);
                    ad.Fill(dt);
                    //LLENO MI DATAGRID CON LOS DATOS DE LAS ETIQUETAS DE LA CAJERA SEGUN EL CICLO FOR
                    DG_etiquetas.DataSource = dt;
                    SumarEtiquetas();
                    excel.Range["A8:A4000"].NumberFormat = "@";
                    excel.Range["A1:E1"].Merge();
                    excel.Range["A1"].Value    = "REPORTE DE ETIQUETAS DEL " + inicio.ToString("dd/MM/yyyy") + " AL " + fin.ToString("dd/MM/yyyy");
                    excel.Range["G2:G2"].Value = "Art. en ceros";
                    excel.Range["G3:G3"].Value = "Sin Etiqueta";
                    excel.Range["G4:G4"].Value = "Etiqueta borrosa";
                    excel.Range["G5:G5"].Value = "Total";

                    excel.Range["H8:H1000"].Cells.NumberFormat = "dd/MM/aaaa";
                    excel.Range["G4:H4"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;

                    excel.Range["H2:H2"].Value = ceros;
                    excel.Range["H3:H3"].Value = sinEtiqueta;
                    excel.Range["H4:H4"].Value = borrosa;
                    excel.Range["H5:H5"].Value = total;

                    int indiceColumna = 0;

                    foreach (DataGridViewColumn col in DG_etiquetas.Columns)
                    {
                        indiceColumna++;
                        excel.Cells[7, indiceColumna] = col.Name;
                    }

                    int indiceFila = 6;

                    foreach (DataGridViewRow row in DG_etiquetas.Rows)
                    {
                        indiceFila++;
                        indiceColumna = 0;



                        foreach (DataGridViewColumn col in DG_etiquetas.Columns)
                        {
                            indiceColumna++;

                            excel.Cells[indiceFila + 1, indiceColumna] = row.Cells[col.Name].Value.ToString();
                        }
                    }

                    ceros       = 0;
                    sinEtiqueta = 0;
                    borrosa     = 0;
                    total       = 0;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("" + ex);
            }



            con.Close();


            excel.Visible = true;
        }
Beispiel #51
0
        private void butIN_Click(object sender, System.EventArgs e)
        {
            Cursor  = Cursors.WaitCursor;
            namsinh = int.Parse(tu.Text.ToString().Substring(6, 4));
            dtthuoc.Clear();
            dtduoc.Clear();
            string m_tu  = tu.Text.Substring(0, 10);
            string m_den = den.Text.Substring(0, 10);
            string stime = "'" + m.f_ngay + "'";

            if (chkDuoc.Checked || chkDuyet.Checked)
            {
                sql  = "select 0 as loai,a.maql,trim(c.ten)||' '||c.hamluong as ten,c.dang,trunc(b.soluong," + soluongle + ") as soluong";
                sql += ",a.mabn,d.hoten,case when d.phai=0 then to_char(" + namsinh + "-to_number(d.namsinh,'0000')) else '' end as nam,";
                sql += "case when d.phai=1 then to_char(" + namsinh + "-to_number(d.namsinh,'0000')) else '' end as nu,";
                sql += "trim(d.sonha)||' '||trim(d.thon)||' '||trim(g.tenpxa)||' '||trim(f.tenquan)||' '||trim(e.tentt) as diachi,";
                sql += "nullif(a.sothe,'') as sothe,'' as noigioithieu,'' as chandoan_gt,";
                sql += "a.chandoan as chandoan_kkb,'01,' as xutri,nullif(h.hoten,' ') as tenbs,a.maphu as madoituong,2 as nhantu";
                sql += " from xxx.bhytkb a left join xxx.bhytthuoc b on a.id=b.id";
                sql += " inner join " + user + ".d_dmbd c on b.mabd=c.id";
                sql += " inner join " + user + ".btdbn d on a.mabn=d.mabn";
                sql += " inner join " + user + ".btdtt e on d.matt=e.matt";
                sql += " inner join " + user + ".btdquan f on d.maqu=f.maqu";
                sql += " inner join " + user + ".btdpxa g on d.maphuongxa=g.maphuongxa";
                sql += " left join " + user + ".dmbs h on a.mabs=h.ma";
                sql += " where a.id>0";
                if (m_tu != "")
                {
                    sql += " and " + m.for_ngay("a.ngay", stime) + " between to_date('" + m_tu + "'," + stime + ") and to_date('" + m_den + "'," + stime + ")";
                }
                sql += " and a.makp='99'";
                sql += " union all ";
                sql += "select 2 as loai,a.maql,c.ten,c.dvt as dang,1 as soluong ";
                sql += ",a.mabn,d.hoten,case when d.phai=0 then to_char(" + namsinh + "-to_number(d.namsinh,'0000')) else '' end as nam,";
                sql += "case when d.phai=1 then to_char(" + namsinh + "-to_number(d.namsinh,'0000')) else '' end as nu,";
                sql += "trim(d.sonha)||' '||trim(d.thon)||' '||trim(g.tenpxa)||' '||trim(f.tenquan)||' '||trim(e.tentt) as diachi,";
                sql += "nullif(a.sothe,'') as sothe,'' as noigioithieu,'' as chandoan_gt,";
                sql += "a.chandoan as chandoan_kkb,'01,' as xutri,nullif(h.hoten,' ') as tenbs,a.maphu as madoituong,2 as nhantu";
                sql += " from xxx.bhytkb a left join xxx.bhytcls b on a.id=b.id";
                sql += " inner join " + user + ".v_giavp c on b.mavp=c.id";
                sql += " inner join " + user + ".btdbn d on a.mabn=d.mabn";
                sql += " inner join " + user + ".btdtt e on d.matt=e.matt";
                sql += " inner join " + user + ".btdquan f on d.maqu=f.maqu";
                sql += " inner join " + user + ".btdpxa g on d.maphuongxa=g.maphuongxa";
                sql += " left join " + user + ".dmbs h on a.mabs=h.ma";
                sql += " where a.id>0";
                if (m_tu != "")
                {
                    sql += " and " + m.for_ngay("a.ngay", stime) + " between to_date('" + m_tu + "'," + stime + ") and to_date('" + m_den + "'," + stime + ")";
                }
                sql   += " and a.makp='99'";
                dtduoc = d.get_thuoc(tu.Text, den.Text, sql).Tables[0];
            }
            if (chkThuoc.Checked && !chkDuyet.Checked)
            {
                sql  = "select 0 as loai,a.maql,trim(c.ten)||' '||c.hamluong as ten,c.dang,trunc(b.slyeucau," + soluongle + ") as soluong";
                sql += " from xxx.benhancc d inner join xxx.d_thuocbhytll a on d.maql=a.maql";
                sql += " left join xxx.d_thuocbhytct b on a.id=b.id";
                sql += " inner join " + user + ".d_dmbd c on b.mabd=c.id";
                sql += " where d.maql>0";
                if (m_tu != "")
                {
                    sql += " and " + m.for_ngay("d.ngay", stime) + " between to_date('" + m_tu + "'," + stime + ") and to_date('" + m_den + "'," + stime + ")";
                }
                sql += " union all ";
                sql += " select 1 as loai,a.maql,trim(c.ten)||' '||c.hamluong as ten,c.dang,trunc(b.soluong," + soluongle + ") as soluong";
                sql += " from xxx.d_toathuocll a left join xxx.d_toathuocct b on a.id=b.id";
                sql += " inner join " + user + ".d_dmbd c on b.mabd=c.id";
                sql += " inner join xxx.benhancc d on a.maql=d.maql";
                sql += " where d.maql>0";
                if (m_tu != "")
                {
                    sql += " and " + m.for_ngay("d.ngay", stime) + " between to_date('" + m_tu + "'," + stime + ") and to_date('" + m_den + "'," + stime + ")";
                }
                sql += " union all ";
                sql += "select 2 as loai,a.maql,b.ten,b.dvt as dang,1 as soluong ";
                sql += " from xxx.v_chidinh a inner join " + user + ".v_giavp b on a.mavp=b.id";
                sql += " inner join xxx.benhancc d on a.maql=d.maql";
                sql += " where d.maql>0";
                if (m_tu != "")
                {
                    sql += " and " + m.for_ngay("d.ngay", stime) + " between to_date('" + m_tu + "'," + stime + ") and to_date('" + m_den + "'," + stime + ")";
                }
                dtthuoc = m.get_data_mmyy(sql, m_tu, m_den, false).Tables[0];
            }
            sql  = "select a.maql,a.mabn,c.hoten,case when c.phai=0 then to_char(" + namsinh + "-to_number(c.namsinh,'0000')) else '' end as nam,";
            sql += "case when c.phai=1 then to_char(" + namsinh + "-to_number(c.namsinh,'0000')) else '' end as nu,";
            sql += "trim(c.sonha)||' '||trim(c.thon)||' '||trim(f.tenpxa)||' '||trim(e.tenquan)||' '||trim(d.tentt) as diachi,";
            sql += "nullif(i.sothe,'') as sothe,nullif(h.tenbv,'') as noigioithieu,nullif(g.chandoan,'') as chandoan_gt,";
            sql += "a.chandoan as chandoan_kkb,k.xutri,j.hoten as tenbs,a.madoituong,a.nhantu";
            sql += " from xxx.benhancc a inner join " + user + ".btdbn c on a.mabn=c.mabn";
            sql += " inner join " + user + ".btdtt d on c.matt=d.matt";
            sql += " inner join " + user + ".btdquan e on c.maqu=e.maqu";
            sql += " inner join " + user + ".btdpxa f on c.maphuongxa=f.maphuongxa";
            sql += " left join " + user + ".noigioithieu g on a.maql=g.maql";
            sql += " left join " + user + ".dstt h on g.mabv=h.mabv";
            sql += " left join xxx.bhyt i on a.maql=i.maql";
            sql += " left join " + user + ".dmbs j on a.mabs=j.ma";
            sql += " left join xxx.xutrikbct k on a.maql=k.maql";//xuatvien b
            sql += " where a.maql>0 ";
            if (m_tu != "")
            {
                sql += " and " + m.for_ngay("a.ngay", stime) + " between to_date('" + m_tu + "'," + stime + ") and to_date('" + m_den + "'," + stime + ")";
            }
            ds = m.get_data_mmyy(sql, m_tu, m_den, false);
            taotable();
            get_data(ds.Tables[0], 1, false);

            if (chkDuoc.Checked)
            {
                get_data(dtduoc, dsxml.Tables[0].Rows.Count + 1, true);
            }
            Cursor = Cursors.Default;
            if (dsxml.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
                return;
            }
            exp_excel();
        }
Beispiel #52
0
        private void save_excel(int mode)
        {
            if (mode == 1)
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter           = "Excel files (*.xls)|*.xls";
                saveFileDialog.FilterIndex      = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt     = true;
                saveFileDialog.Title            = "导出Excel文件到";

                DateTime now = DateTime.Now;
                saveFileDialog.FileName = now.Year.ToString().PadLeft(2)
                                          + now.Month.ToString().PadLeft(2, '0')
                                          + now.Day.ToString().PadLeft(2, '0') + "-"
                                          + now.Hour.ToString().PadLeft(2, '0')
                                          + now.Minute.ToString().PadLeft(2, '0')
                                          + now.Second.ToString().PadLeft(2, '0');
                saveFileDialog.ShowDialog();

                Stream       myStream = saveFileDialog.OpenFile();
                StreamWriter sw       = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
                string       str      = "";
                try
                {
                    //写标题
                    for (int i = 0; i < this.dataGridView1.ColumnCount; i++)
                    {
                        if (i > 0)
                        {
                            str += ",";
                        }
                        str += this.dataGridView1.Columns[i].HeaderText;
                    }
                    str = "\t" + str;
                    sw.WriteLine(str);
                    //写内容
                    for (int j = 0; j < this.dataGridView1.Rows.Count; j++)
                    {
                        string tempStr = "";
                        for (int k = 0; k < this.dataGridView1.Columns.Count; k++)
                        {
                            if (k > 0)
                            {
                                tempStr += ",";
                            }
                            tempStr += this.dataGridView1.Rows[j].Cells[k].Value.ToString();
                        }
                        tempStr = (j + 1).ToString() + "," + tempStr;
                        sw.WriteLine(tempStr);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception)
                {
                    //MessageBox.Show(ex.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
            else if (mode == 0)
            {
                string[,] data = new string[this.dataGridView1.Rows.Count - 1, this.dataGridView1.Columns.Count];
                for (int i = 0; i < this.dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < this.dataGridView1.Columns.Count; j++)
                    {
                        data[i, j] = this.dataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }

                while (data[0, 0] != null)
                {
                    string[,] temp_same_date = new string[data.GetLength(0), data.GetLength(1)];
                    string[,] temp_diff_date = new string[data.GetLength(0), data.GetLength(1)];
                    int g = 0;
                    int h = 0;
                    for (int k = 0; k < data.GetLength(0); k++)
                    {
                        string date_temp = data[0, 1];

                        if (data[k, 1] != date_temp)
                        {
                            temp_diff_date[g, 0] = data[k, 0];
                            temp_diff_date[g, 1] = data[k, 1];
                            temp_diff_date[g, 2] = data[k, 2];
                            g++;
                        }
                        else
                        {
                            temp_same_date[h, 0] = data[k, 0];
                            temp_same_date[h, 1] = data[k, 1];
                            temp_same_date[h, 2] = data[k, 2];
                            h++;
                        }
                    }
                    data = new string[temp_diff_date.GetLength(0), temp_diff_date.GetLength(1)];
                    for (int k = 0; k < temp_diff_date.GetLength(0); k++)
                    {
                        data[k, 0] = temp_diff_date[k, 0];
                        data[k, 1] = temp_diff_date[k, 1];
                        data[k, 2] = temp_diff_date[k, 2];
                    }

                    string save_file_name = this.textBox1.Text + "\\" + "report\\" + temp_same_date[0, 1] + ".xls";
                    string save_file_path = this.textBox1.Text + "\\" + "report\\";
                    Directory.CreateDirectory(save_file_path);
                    if (!File.Exists(save_file_name))
                    {
                        File.Create(save_file_name).Close();
                    }

                    StreamWriter sw  = new StreamWriter(save_file_name, false, Encoding.UTF8);
                    string       str = "";
                    //写标题
                    str = "," + "序列号" + "," + "日期" + "," + "时间";
                    sw.WriteLine(str);

                    //写内容
                    for (int j = 0; j < temp_same_date.GetLength(0); j++)
                    {
                        string tempStr = "";
                        for (int k = 0; k < temp_same_date.GetLength(1); k++)
                        {
                            if (k > 0)
                            {
                                tempStr += ",";
                            }
                            tempStr += temp_same_date[j, k];
                        }
                        if (tempStr != ",,")
                        {
                            tempStr = (j + 1).ToString() + "," + tempStr;
                            sw.WriteLine(tempStr);
                        }
                    }
                    sw.Flush();
                    sw.Close();
                }
            }
            else if (mode == 2)
            {
                System.Data.DataTable dt = (System.Data.DataTable)dataGridView1.DataSource;
                if (dt == null || dt.Rows.Count == 0)
                {
                    return;
                }

                // 创建Excel文档,保存格式 Office2007 xlsx.
                // 需要引用:Microsoft.Office.Interop.Excel.dll 12.0版本的支持Office2007.
                while (dt.Rows[0][0] != "")
                {
                    string[,] temp_same_date = new string[dt.Rows.Count, dt.Columns.Count];         //定义2维数组长度
                    string[,] temp_diff_date = new string[dt.Rows.Count, dt.Columns.Count];         //定义2维数组长度
                    int g = 0;
                    int h = 0;
                    for (int k = 0; k < dt.Rows.Count; k++)
                    {
                        string date_temp = dt.Rows[0][1].ToString();
                        if (date_temp == "")
                        {
                            return;
                        }

                        if (dt.Rows[k][1].ToString() != date_temp)
                        {
                            temp_diff_date[g, 0] = dt.Rows[k][0].ToString();
                            temp_diff_date[g, 1] = dt.Rows[k][1].ToString();
                            temp_diff_date[g, 2] = dt.Rows[k][2].ToString();
                            g++;
                        }
                        else
                        {
                            temp_same_date[h, 0] = dt.Rows[k][0].ToString();
                            temp_same_date[h, 1] = dt.Rows[k][1].ToString();
                            temp_same_date[h, 2] = dt.Rows[k][2].ToString();
                            h++;
                        }
                    }
                    //temp_same_date = temp_same_date.Where(s => !string.IsNullOrEmpty(s)).ToArray();
                    dt.Clear();
                    for (int k = 0; k < temp_diff_date.GetLength(0); k++)    //将不同的数据重新写入表格中
                    {
                        DataRow dr = dt.NewRow();
                        for (int j = 0; j < temp_diff_date.GetLength(1); j++)
                        {
                            dr[j] = temp_diff_date[k, j];
                        }
                        dt.Rows.Add(dr);
                    }

                    /*
                     * for (int k = 0; k < temp_diff_date.GetLength(0); k++)
                     * {
                     *  dt.Rows[k][0] = temp_diff_date[k, 0];
                     *  dt.Rows[k][1] = temp_diff_date[k, 1];
                     *  dt.Rows[k][2] = temp_diff_date[k, 2];
                     * }
                     */
                    string save_file_name = this.textBox1.Text + "\\" + "report\\" + temp_same_date[0, 1] + ".xlsx";
                    string save_file_path = this.textBox1.Text + "\\" + "report\\";
                    Directory.CreateDirectory(save_file_path);

                    /*
                     * if (!File.Exists(save_file_name))
                     *  File.Create(save_file_name).Close();
                     * else
                     * {
                     *  System.IO.File.Delete(save_file_name);
                     * }
                     */
                    if (File.Exists(save_file_name))
                    {
                        System.IO.File.Delete(save_file_name);     //如果原始excel文件存在,删除该文件
                    }
                    Microsoft.Office.Interop.Excel.Application excel     = new Microsoft.Office.Interop.Excel.Application();
                    Microsoft.Office.Interop.Excel._Workbook   workBook  = excel.Workbooks.Add();                                           //新建文件
                    Microsoft.Office.Interop.Excel._Worksheet  workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet; //新建sheet
                    object misValue = System.Reflection.Missing.Value;

                    //excel.DisplayAlerts = false; // 不显示告警

                    int   rowIndex;
                    int   colIndex;
                    int   page;
                    Range range_1, range_2, range_3;
                    //取得标题并保存
                    String Current_date = DateTime.Now.ToString("yyyyMMdd");
                    String Number       = "记录单编码:  " + Current_date + "0001";

                    /*
                     * workSheet.Cells[1, 1] = "电池序列号记录单";
                     * Range range_1 = (Range)workSheet.get_Range("A1", "E1");
                     * range_1.Merge(0);
                     * range_1.HorizontalAlignment = XlVAlign.xlVAlignCenter;
                     * workSheet.Cells[1, 6] = "记录单编码:  " + Current_date + "0001";
                     * Range range_2 = (Range)workSheet.get_Range("F1", "I1");
                     * range_2.Merge(0);
                     * range_2.HorizontalAlignment = XlVAlign.xlVAlignCenter;
                     */
                    int same_date_length = 0;
                    for (h = 0; h < temp_same_date.GetLength(0); h++)
                    {
                        if (temp_same_date[h, 0] != null)
                        {
                            same_date_length++;
                        }
                    }
                    if (same_date_length % 100 == 0)
                    {
                        page = same_date_length / 100;
                    }
                    else
                    {
                        page = same_date_length / 100 + 1;
                    }
                    for (int k = 0; k < page; k++)
                    {
                        rowIndex = 1;
                        colIndex = 0;
                        workSheet.Cells[54 + k * 58, 7] = "签名及日期:";
                        ///合并单元格
                        workSheet.Cells[1 + k * 58, 1] = "电池序列号记录单";
                        workSheet.Cells[1 + k * 58, 6] = Number;
                        range_1 = (Range)workSheet.get_Range("A" + Convert.ToString(1 + k * 58), "E" + Convert.ToString(1 + k * 58));
                        range_1.Merge(0);
                        range_1.HorizontalAlignment = XlVAlign.xlVAlignCenter;
                        range_2 = (Range)workSheet.get_Range("F" + Convert.ToString(1 + k * 58), "I" + Convert.ToString(1 + k * 58));
                        range_2.Merge(0);
                        range_2.HorizontalAlignment = XlVAlign.xlVAlignCenter;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            colIndex++;
                            workSheet.Cells[2 + k * 58, colIndex + 1] = dt.Columns[i].ColumnName;  //保存标题
                            workSheet.Cells[2 + k * 58, colIndex + 6] = dt.Columns[i].ColumnName;  //保存标题
                            workSheet.Cells[2 + k * 58, 1]            = "序号";
                            workSheet.Cells[2 + k * 58, 6]            = "序号";
                        }

                        //保存数据到execl
                        for (int i = k * 100; i < (k + 1) * 100; i++)
                        {
                            rowIndex++;
                            colIndex = 0;

                            /*
                             * if (temp_same_date[i, 1] != null)
                             * {
                             *  workSheet.Cells[rowIndex+1, 1] = i + 1;        //保存数量编号到第一列
                             * }
                             *
                             */
                            if (i % 100 < 50 && i < same_date_length)
                            {
                                workSheet.Cells[rowIndex + 1 + k * 58, 1] = i + 1;
                            }

                            else
                            if (i < same_date_length)
                            {
                                workSheet.Cells[rowIndex + 1 + k * 58 - 50, 6] = i + 1;
                            }
                            for (int j = 0; j < temp_same_date.GetLength(1); j++)
                            {
                                colIndex++;
                                if (i % 100 < 50)
                                {
                                    workSheet.Cells[rowIndex + 1 + k * 58, colIndex + 1] = temp_same_date[i, j];             //保存具体数据到excel
                                }
                                else
                                {
                                    workSheet.Cells[rowIndex + 1 + k * 58 - 50, colIndex + 6] = temp_same_date[i, j];
                                }
                                //workSheet.Cells.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle =Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                            }
                        }

                        //画边框,字体居中
                        range_3 = (Range)workSheet.get_Range("A" + Convert.ToString(1 + k * 58), "I" + Convert.ToString(52 + k * 58));
                        //横向居中
                        range_3.HorizontalAlignment = XlVAlign.xlVAlignCenter;
                        ///字体大小
                        range_3.Font.Size = 10;
                        ///字体
                        range_3.Font.Name = "黑体";
                        ///行高
                        //range_3.RowHeight = 24;
                        //自动调整列宽
                        range_3.EntireColumn.AutoFit();
                        //填充颜色
                        //range_3.Interior.ColorIndex = 20;
                        //设置单元格边框的粗细
                        range_3.Cells.Borders.LineStyle = 1;
                    }



                    //文件保存
                    workSheet.SaveAs(save_file_name); //保存文件
                    workBook.Close();                 //关闭引用
                    excel.Quit();                     //退出excel

                    PublicMethod.Kill(excel);         //调用kill当前excel进程
                    releaseObject(workSheet);         //释放COM对象
                    releaseObject(workBook);
                    releaseObject(excel);

                    GC.Collect();
                    //MessageBox.Show(string.Format("{0} 文档生成成功.", filePath));
                }
                return;
            }
        }
        public void TBS(string date_1, string date_2, System.Data.DataTable dt)
        {
            var GR = new General_Reestr();

            string path = AppDomain.CurrentDomain.BaseDirectory + @"ReportTemplates\Итог по станции.xlsx";

            using (SLDocument sl = new SLDocument(path))
            {
                sl.SelectWorksheet("Итоговая  справка");

                sl.SetCellValue("B6", "c " + date_1 + " по " + date_2 + "на ТОО \"Batys Petroleum\"");

                var val = dt.Rows.Count * 2 + 11;
                sl.CopyCell("B13", "H24", "B" + val, true);

                int item = 0;

                //Кол.во услуг
                int total = 0;

                //Сумм * Кол.во
                double final_sum = 0;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i % 2 == 0)
                    {
                        var k = 11 + item;

                        sl.MergeWorksheetCells(i + k, 2, i + k, 8);

                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (j == 0)
                            {
                                sl.SetCellValue(i + k, j + 2, dt.Rows[i][j].ToString());
                                sl.SetCellStyle(i + k, j + 2, GR.FormattingExcelCells(sl, false));
                            }
                            else
                            {
                                sl.SetCellValue(i + k, j + 8, Convert.ToDecimal(dt.Rows[i][j].ToString()));
                                sl.SetCellStyle(i + k, j + 8, GR.FormattingExcelCells(sl, false));
                            }
                        }
                    }
                    else
                    {
                        var k = 11 + item;
                        //Excel.Range range = worksheet.Range[worksheet.Cells[i + k, 2], worksheet.Cells[i + k, 8]];
                        //range.Merge();

                        sl.MergeWorksheetCells(i + k, 2, i + k, 8);

                        //FormattingExcelCells(range, false, false);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (j == 0)
                            {
                                sl.SetCellValue(i + k, j + 2, dt.Rows[i][j].ToString());
                                sl.SetCellStyle(i + k, j + 2, GR.FormattingExcelCells(sl, false));
                            }
                            else
                            {
                                sl.SetCellValue(i + k, j + 8, Convert.ToDecimal(dt.Rows[i][j].ToString()));
                                sl.SetCellStyle(i + k, j + 8, GR.FormattingExcelCells(sl, false));
                            }
                        }
                    }

                    final_sum += int.Parse(dt.Rows[i][1].ToString()) * double.Parse(dt.Rows[i][2].ToString());;

                    if (i < dt.Rows.Count)
                    {
                        total += int.Parse(dt.Rows[i][1].ToString());
                    }
                    else
                    {
                        continue;
                    }

                    //backgroundWorker.ReportProgress(i);
                    item++;
                }
                //Кол.во обработанных
                sl.SetCellValue("I8", total);

                //Итоговая сумма
                sl.SetCellValue(dt.Rows.Count + 11 + item, 10, final_sum);
                sl.SetCellStyle(dt.Rows.Count + 11 + item, 10, GR.FormattingExcelCells(sl, false));

                sl.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"Report\Итог по станции.xlsx");
            }

            dt.Clear();

            Process.Start(AppDomain.CurrentDomain.BaseDirectory + @"Report\Итог по станции.xlsx");
        }
Beispiel #54
0
        static void Main(string[] args)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Clear();
            Application app       = new Application();  //загружаем исходную таблицу
            Workbook    workbook  = app.Workbooks.Open("D:/Netology/Job/Tasks/Task_4/Task_4/ФайлСИсходнымиДанными.xls");
            var         worksheet = workbook.Sheets[1];
            Range       range     = worksheet.UsedRange;

            object[,] data = range.Value2;

            int cCnt = range.Columns.Count;
            int rCnt = range.Rows.Count;

            int row;
            int col;

            for (col = 1; col <= cCnt; col++)
            {
                dt.Columns.Add(col.ToString(), typeof(string));

                for (row = 1; row <= rCnt; row++)
                {
                    dt.Rows.Add();
                    dt.Rows[row - 1][col - 1] = data[row, col];
                }
            }

            System.Data.DataTable strucData = new System.Data.DataTable();      //редактируем её для удобства
            strucData.Columns.Add("Код счёта бюджетного учёта", typeof(string));
            strucData.Columns.Add("Номер банковского (лицевого) счета", typeof(string));
            strucData.Columns.Add("Остаток средств на начало года на счёте", typeof(string));
            strucData.Columns.Add("Средства в пути на начало года", typeof(string));
            strucData.Columns.Add("Остаток средств на счете на конец года", typeof(string));
            strucData.Columns.Add("Средства в пути на отчетную дату (в рублях)", typeof(string));
            for (row = 4; row <= ((dt.Rows.Count / dt.Columns.Count) - 1); row++)
            {
                strucData.Rows.Add();
                strucData.Rows[row - 4][0] = "1" + dt.Rows[row][1].ToString().Remove(dt.Rows[row][1].ToString().Length - 3, 3) + "000";
                strucData.Rows[row - 4][1] = dt.Rows[row][0].ToString();
                for (int r1 = 2; r1 < strucData.Columns.Count; r1++)
                {
                    strucData.Rows[row - 4][r1] = dt.Rows[row][r1].ToString();
                }
            }
            DataView dataView = new DataView(strucData);      //сортируем таблицу

            dataView.Sort = "Код счёта бюджетного учёта";
            System.Data.DataTable sortData = dataView.ToTable();
            app.Quit();


            XDocument xdoc    = new XDocument(new XDeclaration("1.0", Encoding.GetEncoding("windows-1251").WebName, ""));   //записываем в xml
            XElement  RootXml = new XElement("RootXml");

            XElement   SchemaVersion = new XElement("SchemaVersion");
            XAttribute Number        = new XAttribute("Number", "2");

            SchemaVersion.Add(Number);

            XElement   Period = new XElement("Period");
            XAttribute Date   = new XAttribute("Date", "2014-02-06");

            Period.Add(Date);

            XElement   Source    = new XElement("Source");
            XAttribute ClassCode = new XAttribute("ClassCode", "ДМС");
            XAttribute Code      = new XAttribute("Code", "819");

            Source.Add(Code);
            Source.Add(ClassCode);

            XElement   Form   = new XElement("Form");
            XAttribute Code1  = new XAttribute("Code", "178");
            XAttribute Name1  = new XAttribute("Name", "Счета в кредитных организациях");
            XAttribute Status = new XAttribute("Status", "0");

            Form.Add(Status);
            Form.Add(Name1);
            Form.Add(Code1);

            for (int i = 1; i < sortData.Columns.Count; i++)
            {
                XElement   Column = new XElement("Column");
                XAttribute Num    = new XAttribute("Num", i.ToString());
                XAttribute Name   = new XAttribute("Name", sortData.Columns[i].ToString());
                Column.Add(Name);
                Column.Add(Num);
                Form.Add(Column);
            }

            String     CurentDoc = "";//sortData.Rows[0][0].ToString();
            XElement   Doc       = new XElement("Document");
            XAttribute PL        = new XAttribute("ПлСч11", CurentDoc);

            for (int i = 0, numStr = 1; i < sortData.Rows.Count; i++, numStr++)
            {
                string str = numStr.ToString();
                for (int s = str.Length; s < 3; s++)
                {
                    str = str.Insert(0, "0");
                }
                string nextDoc = sortData.Rows[i][0].ToString();
                if (CurentDoc == "")
                {
                    CurentDoc = nextDoc;
                    PL.Value  = nextDoc;
                    Doc.Add(PL);
                }
                if (CurentDoc == nextDoc)
                {
                    XElement   Data    = new XElement("Data");
                    XAttribute strData = new XAttribute("СТРОКА", str);
                    for (int j = 1; j < sortData.Columns.Count; j++)
                    {
                        XElement   Px    = new XElement("Px");
                        XAttribute Num   = new XAttribute("Num", j.ToString());
                        XAttribute Value = new XAttribute("Value", sortData.Rows[i][j].ToString());
                        Px.Add(Value);
                        Px.Add(Num);
                        Data.Add(Px);
                    }
                    Data.Add(strData);
                    Doc.Add(Data);
                    if (i == sortData.Rows.Count - 1)
                    {
                        Data    = new XElement("Data");
                        strData = new XAttribute("СТРОКА", "960");
                        for (int j = 2; j < sortData.Columns.Count; j++)
                        {
                            XElement   Px  = new XElement("Px");
                            XAttribute Num = new XAttribute("Num", j.ToString());
                            float      val = 0;
                            foreach (XElement px in Doc.Elements("Data").Elements("Px"))
                            {
                                if (px.Attribute("Num").Value.ToString() == j.ToString())
                                {
                                    val += float.Parse(px.Attribute("Value").Value.ToString());
                                }
                            }
                            XAttribute Value = new XAttribute("Value", val.ToString());
                            Px.Add(Value);
                            Px.Add(Num);
                            Data.Add(Px);
                        }
                        Data.Add(strData);
                        Doc.Add(Data);
                        Form.Add(Doc);
                    }
                }
                else
                {
                    XElement   Data    = new XElement("Data");
                    XAttribute strData = new XAttribute("СТРОКА", "960");
                    for (int j = 2; j < sortData.Columns.Count; j++)
                    {
                        XElement   Px  = new XElement("Px");
                        XAttribute Num = new XAttribute("Num", j.ToString());
                        float      val = 0;
                        foreach (XElement px in Doc.Elements("Data").Elements("Px"))
                        {
                            if (px.Attribute("Num").Value.ToString() == j.ToString())
                            {
                                val += float.Parse(px.Attribute("Value").Value.ToString());
                            }
                        }
                        XAttribute Value = new XAttribute("Value", val.ToString());
                        Px.Add(Value);
                        Px.Add(Num);
                        Data.Add(Px);
                    }
                    Data.Add(strData);
                    Doc.Add(Data);
                    Form.Add(Doc);
                    numStr = 0;
                    i--;
                    Doc       = new XElement("Document");
                    PL        = new XAttribute("ПлСч11", nextDoc);
                    CurentDoc = nextDoc;
                    Doc.Add(PL);
                }
            }
            RootXml.Add(SchemaVersion);
            SchemaVersion.Add(Period);
            Period.Add(Source);
            Source.Add(Form);
            xdoc.Add(RootXml);

            xdoc.Save("ФайлРезультат.xml");     //сохраняем

            for (int i = 0; i < sortData.Columns.Count; i++)
            {
                Console.Write("{0,35}", sortData.Columns[i].ToString());
            }
            Console.Write("\n");
            for (int i = 0; i < sortData.Rows.Count; i++)
            {
                for (int j = 0; j < sortData.Columns.Count; j++)
                {
                    Console.Write("{0,35}", sortData.Rows[i][j].ToString());
                }
                Console.Write("\n");
            }
            Console.ReadKey();
        }
Beispiel #55
0
        public void btnDone_Click(object sender, EventArgs e)
        {
            //Добавление в таблицу продажа
            if (dataGridView1.Rows.Count == 0)
            {
                MessageBox.Show("Корзина пустая");
                return;
            }
            DateTime     myDateTime       = DateTime.Now;
            string       sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
            string       query1           = $"INSERT INTO prodaja (data_vremya_prodaji) VALUES ('{sqlFormattedDate}')";
            MySqlCommand command1         = new MySqlCommand(query1, conn);

            conn.Open();
            try
            {
                command1.ExecuteNonQuery();
            }
            catch
            {
                MessageBox.Show("Ошибка формирования покупки");
                return;
            }
            conn.Close();

            //Поиск номера чека
            MySqlDataAdapter adapter3 = new MySqlDataAdapter($"SELECT nomer_checka FROM prodaja", conn);

            table3.Clear();
            adapter3.Fill(table3);
            nomchek = table3.Rows[table3.Rows.Count - 1][0].ToString();

            //Добавление в таблицу чек
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                string       query2   = $"INSERT INTO chek (nomer_checka, artikul_lekarstva, kolvo_lekarstva, price) VALUES ('{nomchek}', '{dataGridView1.Rows[i].Cells[0].Value.ToString()}', '{dataGridView1.Rows[i].Cells[2].Value.ToString()}', '{dataGridView1.Rows[i].Cells[3].Value.ToString()}')";
                MySqlCommand command2 = new MySqlCommand(query2, conn);
                conn.Open();
                try
                {
                    command2.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("Ошибка формирования покупки");
                    return;
                }
                //Удаление купленного товара из базы
                string       query3   = $"UPDATE lekarstva SET kolvo = kolvo - {dataGridView1.Rows[i].Cells[2].Value.ToString()} WHERE artikul_lekarstva = {dataGridView1.Rows[i].Cells[0].Value.ToString()}";
                MySqlCommand command3 = new MySqlCommand(query3, conn);
                try
                {
                    command3.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("Ошибка обновления количества");
                    return;
                }
                conn.Close();
            }
            printPreviewDialog1.ShowDialog();
            MessageBox.Show("Покупка совершена");
            ClearAll();
        }
Beispiel #56
0
        //INITIALIZE, CONNECT, CONSTRUCT DATATABLES
        public void subInit(string[] selectedReg)
        {
            //Set core
            try
            {
                objCore = new FRRJIf.Core();

                objSelectedReg = new FRRJIf.DataNumReg[selectedReg.Length];

                //parse string[] to int[]
                selectedRegArr = Array.ConvertAll(selectedReg, int.Parse);

                //tasklist return object
                taskArr = new string[10];

                //init datatable return objects
                dt_Selected = new System.Data.DataTable();
                dt_Selected.Clear();
                dt_Selected.Columns.Add("id", typeof(int));
                dt_Selected.Columns.Add("Registry", typeof(int));
                dt_Selected.Columns.Add("Value", typeof(double));
                dt_Selected.Columns.Add("Selected", typeof(bool));

                //welding params
                voltage = 0;
                amp     = 0;

                try
                {
                    //Set FANUC datatable1
                    objDataTable1 = objCore.get_DataTable();

                    //robotinfo
                    numRegJob    = objDataTable1.AddNumReg(FRRJIf.FRIF_DATA_TYPE.NUMREG_INT, 2, 2);
                    numRegofJob  = objDataTable1.AddNumReg(FRRJIf.FRIF_DATA_TYPE.NUMREG_INT, 3, 3);
                    objRobotName = objDataTable1.AddNumReg(FRRJIf.FRIF_DATA_TYPE.NUMREG_INT, 5, 5);

                    //selected numregs
                    for (int i = 0; i < objSelectedReg.Length; i++)
                    {
                        objSelectedReg[i] = objDataTable1.AddNumReg(FRRJIf.FRIF_DATA_TYPE.NUMREG_INT, selectedRegArr[i], selectedRegArr[i]);
                    }

                    //10 data tasks
                    objTaskList = new FRRJIf.DataTask[10];
                    for (int i = 0; i < objTaskList.Length; i++)
                    {
                        objTaskList[i] = objDataTable1.AddTask(FRRJIf.FRIF_DATA_TYPE.TASK, i + 1);
                    }
                    isInit = true;
                    return;
                }
                catch (Exception ex)
                {
                    //handle ex
                    Console.WriteLine(ex.ToString());
                }
            }
            catch
            {
                Console.WriteLine("error..");
            }
        }
Beispiel #57
0
        private void SapXepData(DataSet ds)
        {
            try
            {
                t_deNghiThanhToan = 0;
                System.Data.DataSet dts_ = ds;
                //  dsChungC79_80_a_HD.Tables.Clear();
                // System.Data.DataSet dssx = ds;
                string s_stt = "";
                string m_mabv = "", s = "", s_sothe = "";
                // m_mabv = d.MABV_BHYT.ToString();
                string[] arr;
                DataRow  r1, r2;
                int      i_stt = 1;
                dtChungC79_80_a_HD.Clear();
                int    manhomthe = 0;
                string s_tennhom = "";
                int    i_sttA    = 0;
                foreach (DataRow r in dts_.Tables[0].Select("", "tennhom,tenvp"))
                {
                    r1 = d.getrowbyid(dtChungC79_80_a_HD, "TENNHOM");
                    if (r1 == null)
                    {
                        if (r["tennhom"].ToString().Equals(s_tennhom.ToString()) == false)
                        {
                            r2           = dtChungC79_80_a_HD.NewRow();
                            r2["dt_stt"] = r["tennhom"];
                            r2["stt"]    = d.getIndex(i_sttA);
                            dtChungC79_80_a_HD.Rows.Add(r2);
                            s_tennhom = r["tennhom"].ToString();
                            i_sttA++;
                            manhomthe++;
                        }
                        r2        = dtChungC79_80_a_HD.NewRow();
                        r2["stt"] = i_stt;
                        i_stt++;
                        //      r2["tennhom"] = r["tennhom"];
                        r2["dt_stt"] = r["dt_stt"];
                        r2["dt_bv"]  = r["dt_bv"];

                        r2["dt_ngay"]      = r["dt_ngay"];
                        r2["dt_nhom"]      = r["dt_nhom"];
                        r2["masobyt"]      = r["masobyt"];
                        r2["tenhc"]        = r["tenhc"];
                        r2["tenvp"]        = r["tenvp"];
                        r2["duongdung"]    = r["duongdung"];
                        r2["dangbaoche"]   = r["dangbaoche"];
                        r2["hamluong"]     = r["hamluong"];
                        r2["dangtrinhbay"] = r["dangtrinhbay"];
                        r2["tenhang"]      = r["tenhang"];
                        r2["tennuoc"]      = r["tennuoc"];
                        r2["donvi"]        = r["donvi"];
                        r2["giamua"]       = r["giamua"];
                        r2["giattbh"]      = r["giattbh"];
                        r2["slngoaitru"]   = r["slngoaitru"];
                        r2["slnoitru"]     = r["slnoitru"];

                        r2["thanhtien"] = double.Parse(r["thanhtien_ngoaitru"].ToString()) + double.Parse(r["thanhtien_noitru"].ToString());

                        r2["ghichu"] = r["ghichu"];


                        dtChungC79_80_a_HD.Rows.Add(r2);
                    }
                    else
                    {
                        if (r["tennhom"].ToString().Equals(s_tennhom.ToString()) == false)
                        {
                            r2           = dtChungC79_80_a_HD.NewRow();
                            r2["dt_stt"] = r["tennhom"];
                            r2["stt"]    = d.getIndex(i_sttA);
                            dtChungC79_80_a_HD.Rows.Add(r2);
                            s_tennhom = r["tennhom"].ToString();
                            i_sttA++;
                            manhomthe++;
                        }
                        r2        = dtChungC79_80_a_HD.NewRow();
                        r2["stt"] = i_stt;
                        i_stt++;
                        //      r2["tennhom"] = r["tennhom"];
                        r2["dt_stt"] = r["dt_stt"];
                        r2["dt_bv"]  = r["dt_bv"];

                        r2["dt_ngay"]      = r["dt_ngay"];
                        r2["dt_nhom"]      = r["dt_nhom"];
                        r2["masobyt"]      = r["masobyt"];
                        r2["tenhc"]        = r["tenhc"];
                        r2["tenvp"]        = r["tenvp"];
                        r2["duongdung"]    = r["duongdung"];
                        r2["dangbaoche"]   = r["dangbaoche"];
                        r2["hamluong"]     = r["hamluong"];
                        r2["dangtrinhbay"] = r["dangtrinhbay"];
                        r2["tenhang"]      = r["tenhang"];
                        r2["tennuoc"]      = r["tennuoc"];
                        r2["donvi"]        = r["donvi"];
                        r2["giamua"]       = r["giamua"];
                        r2["giattbh"]      = r["giattbh"];
                        r2["slngoaitru"]   = r["slngoaitru"];
                        r2["slnoitru"]     = r["slnoitru"];

                        r2["thanhtien"] = double.Parse(r["thanhtien_ngoaitru"].ToString()) + double.Parse(r["thanhtien_noitru"].ToString());

                        r2["ghichu"] = r["ghichu"];


                        dtChungC79_80_a_HD.Rows.Add(r2);
                    }
                }
                dtChungC79_80_a_HD.AcceptChanges();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
Beispiel #58
0
        //BACKGROUNDWORKER


        private void  ExtractData()
        {
            System.Data.DataTable data = new System.Data.DataTable();

            Excel._Worksheet objSheet;
            objSheet = Globals.ThisAddIn.Application.ActiveSheet;

            string invoice    = "";
            string valToCell  = "";
            string itemFilter = "";
            string selection  = "";
            string itemId     = Convert.ToString(CboItemlist.SelectedValue);

            //ALL CHECKED
            if (!checkTodos.Checked)
            {
                itemFilter    = " AND LineItem.ItemID = '" + itemId + "' ";
                selection     = itemId;
                objSheet.Name = "SALDO DE CxC " + selection;
            }
            else
            {
                selection     = "TODOS";
                objSheet.Name = "SALDO DE CxC " + selection;
            }



            try
            {
                //Report to BackgroundWorker
                percentage = 10 * 100 / 100;
                bgw.ReportProgress(percentage, 10);

                //STAR BD CONNETION
                dbConn.StartConn();

                if (dbConn.StartConn().State == System.Data.ConnectionState.Open)
                {
                    data.Clear();

                    string query = "SELECT DISTINCT " +
                                   " Customers.Customer_Bill_Name, " +
                                   " JrnlHdr.Reference, " +
                                   " JrnlHdr.TransactionDate, " +
                                   " sum(ABS(JrnlRow.Amount)) as Amount, " +
                                   " sum(JrnlHdr.AmountPaid) as Paid,  " +
                                   " sum(JrnlHdr.MainAmount) as InvoiceAmount" +
                                   " FROM JrnlHdr " +
                                   " INNER JOIN JrnlRow ON JrnlHdr.PostOrder = JrnlRow.PostOrder " +
                                   " INNER JOIN LineItem ON LineItem.ItemRecordNumber = JrnlRow.ItemRecordNumber " +
                                   " INNER JOIN Customers ON Customers.CustomerRecordNumber = JrnlRow.CustomerRecordNumber " +
                                   " WHERE JrnlHdr.JrnlKey_Journal = '3' " +
                                   " AND JrnlHdr.MainAmount > ABS(AmountPaid) " +
                                   " AND JrnlRow.RowType = '0' " +
                                   itemFilter +
                                   " Group by TransactionDate , Customer_Bill_Name, Reference" +
                                   " Order by Customers.Customer_Bill_Name;";


                    //Report to BackgroundWorker
                    percentage = 20 * 100 / 100;
                    bgw.ReportProgress(percentage, 20);

                    //Start query to DB
                    objSheet.Range[objSheet.Cells[1, 1], objSheet.Cells[9999, 10]].Clear();

                    dbConn.Query(query).Fill(data);


                    //Report to BackgroundWorker
                    percentage = 100 * 100 / 100;
                    bgw.ReportProgress(percentage, 100);


                    if (data.Rows.Count > 0)
                    {
                        int i = 0;
                        int n = i;

                        //INI TABLE STYLING

                        //COLOR
                        objSheet.Range[objSheet.Cells[1, 1], objSheet.Cells[data.Rows.Count + 5, 10]].Interior.Color = ColorTranslator.ToOle(Color.White);
                        objSheet.Range[objSheet.Cells[6, 4], objSheet.Cells[data.Rows.Count + 5, 10]].Interior.Color = ColorTranslator.ToOle(Color.WhiteSmoke);
                        objSheet.Range[objSheet.Cells[6, 1], objSheet.Cells[data.Rows.Count + 5, 4]].Interior.Color  = ColorTranslator.ToOle(Color.LemonChiffon);
                        objSheet.Range[objSheet.Cells[1, 1], objSheet.Cells[1, 10]].Interior.Color = ColorTranslator.ToOle(Color.DarkSeaGreen);
                        objSheet.Cells[3, 1].Interior.Color = ColorTranslator.ToOle(Color.DarkSeaGreen);
                        objSheet.Cells[3, 2].Interior.Color = ColorTranslator.ToOle(Color.WhiteSmoke);
                        objSheet.Range[objSheet.Cells[5, 1], objSheet.Cells[5, 10]].Interior.Color = ColorTranslator.ToOle(Color.DarkSeaGreen);

                        //FONT BOLD
                        objSheet.Range[objSheet.Cells[1, 1], objSheet.Cells[1, 10]].EntireRow.Font.Bold = true;
                        objSheet.Cells[3, 1].EntireRow.Font.Bold = true;
                        objSheet.Range[objSheet.Cells[5, 1], objSheet.Cells[5, 10]].EntireRow.Font.Bold = true;

                        //MERGED CELLS
                        objSheet.Range[objSheet.Cells[1, 1], objSheet.Cells[1, 10]].Merge();

                        //TEXT ALIGN
                        objSheet.get_Range("A1", "A1").Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


                        //BORDER
                        objSheet.Range[objSheet.Cells[1, 1], objSheet.Cells[data.Rows.Count + 5, 10]].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                        objSheet.Range[objSheet.Cells[1, 1], objSheet.Cells[data.Rows.Count + 5, 10]].Borders.Weight    = Excel.XlBorderWeight.xlMedium;
                        objSheet.Range[objSheet.Cells[1, 1], objSheet.Cells[data.Rows.Count + 5, 10]].Borders.Color     = ColorTranslator.ToOle(Color.White);

                        //CURRENCY CELLS
                        objSheet.Range[objSheet.Cells[5, 4], objSheet.Cells[data.Rows.Count + 5, 10]].NumberFormat = "#,###.00";

                        //FILTER
                        objSheet.Range[objSheet.Cells[5, 1], objSheet.Cells[5, 10]].Autofilter();

                        //END TABLE STYLING


                        //TABLE HEADER
                        objSheet.Cells[1, 1]  = "SALDO DE CxC POR ITEM ID";
                        objSheet.Cells[3, 1]  = "Selección";
                        objSheet.Cells[3, 2]  = selection;
                        objSheet.Cells[5, 1]  = "Customer";
                        objSheet.Cells[5, 2]  = "Invoice #";
                        objSheet.Cells[5, 3]  = "Date";
                        objSheet.Cells[5, 4]  = "Status";
                        objSheet.Cells[5, 5]  = "0-30";
                        objSheet.Cells[5, 6]  = "31-60";
                        objSheet.Cells[5, 7]  = "61-90";
                        objSheet.Cells[5, 8]  = "91-120";
                        objSheet.Cells[5, 9]  = "120+";
                        objSheet.Cells[5, 10] = "Total";

                        //WORKSHEET NAME
                        objSheet.Name = "SALDO DE CxC " + selection;

                        while (i < data.Rows.Count)
                        {
                            if (data.Rows[i].ItemArray[0] != null)
                            {
                                string dateTrx = data.Rows[i].ItemArray[2].ToString(); //Transaction Date



                                double days = (DateTime.Today - Convert.ToDateTime(dateTrx)).TotalDays; //Days Expired


                                if (invoice != data.Rows[i].ItemArray[0].ToString())
                                {
                                    objSheet.Cells[i + 6, 2] = data.Rows[i].ItemArray[1].ToString(); //Invoice Number
                                    invoice = data.Rows[i].ItemArray[1].ToString();

                                    n = i;
                                }

                                objSheet.Cells[n + 6, 1] = data.Rows[i].ItemArray[0].ToString();               //Customers
                                objSheet.Cells[n + 6, 3] = Convert.ToDateTime(dateTrx).ToString("yyyy-MM-dd"); //Transaction Date


                                //Report to BackgroundWorker
                                percentage = i * 100 / data.Rows.Count;
                                bgw.ReportProgress(percentage, i);



                                if (Convert.ToDouble(data.Rows[i].ItemArray[4]) == 0.00)
                                {
                                    objSheet.Cells[n + 6, 4] = "Pendiente de pago"; //Status
                                    objSheet.Cells[n + 6, 4].Interior.Color = ColorTranslator.ToOle(Color.LightSalmon);
                                }
                                else
                                {
                                    objSheet.Cells[n + 6, 4] = "Parcialmente pagado"; //Status
                                    objSheet.Cells[n + 6, 4].Interior.Color = ColorTranslator.ToOle(Color.LightGreen);
                                }



                                //EXPIRE DAY
                                if (days <= 30)
                                {
                                    valToCell = SumValue((objSheet.Cells[n + 6, 5] as Excel.Range).Value, data.Rows[i].ItemArray[3].ToString());

                                    objSheet.Cells[n + 6, 5] = valToCell;
                                }
                                if (days > 30 & days <= 60)
                                {
                                    valToCell = SumValue((objSheet.Cells[n + 6, 6] as Excel.Range).Value, data.Rows[i].ItemArray[3].ToString());

                                    objSheet.Cells[n + 6, 6] = valToCell;
                                }
                                if (days > 60 & days <= 90)
                                {
                                    valToCell = SumValue((objSheet.Cells[n + 6, 7] as Excel.Range).Value, data.Rows[i].ItemArray[3].ToString());

                                    objSheet.Cells[n + 6, 7] = valToCell;
                                }
                                if (days > 90 & days <= 120)
                                {
                                    valToCell = SumValue((objSheet.Cells[n + 6, 8] as Excel.Range).Value, data.Rows[i].ItemArray[3].ToString());

                                    objSheet.Cells[n + 6, 8] = valToCell;
                                }

                                if (days > 120)
                                {
                                    valToCell = SumValue((objSheet.Cells[n + 6, 9] as Excel.Range).Value, data.Rows[i].ItemArray[3].ToString());

                                    objSheet.Cells[n + 6, 9] = valToCell;
                                }


                                objSheet.Cells[n + 6, 10].Formula = "=Sum(E" + (n + 6) + ":I" + (n + 6) + ")"; //Total


                                i++;
                            }
                        }
                    }
                    else
                    {
                        MessageBox.Show("No existen datos que procesar para esta seleccion");
                    }

                    //elimina lineas en blanco
                    Excel.Range range    = objSheet.UsedRange;
                    int         rowcount = range.Rows.Count;
                    for (int l = 6; l < rowcount; l++)
                    {
                        Excel.Range rg = objSheet.get_Range("A" + l.ToString());
                        if (Convert.ToString(rg.Value2) == null)
                        {
                            ((Excel.Range)objSheet.Range["A" + l.ToString(), "Z" + l.ToString()]).EntireRow.Delete(null);
                            l--;
                            rowcount--;
                        }
                    }



                    //ACOMODA LAS CELDAS
                    objSheet.Columns.AutoFit();

                    //Close ProgresssBar
                    proBar.FinishProcess();
                }
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }
Beispiel #59
0
        private System.Data.DataTable taxDetails4Print(List<invoiceoutdetail> IODetails, string documentID)
        {
            int HSNLength = 0;
            System.Data.DataTable dt = new System.Data.DataTable();
            System.Data.DataTable dtTax = new System.Data.DataTable();
            try
            {
                if (documentID == "PRODUCTINVOICE")
                {
                    HSNLength = 4;
                }
                else if (documentID == "SERVICEINVOICE")
                {
                    HSNLength = 6;
                }

                {
                    dt.Columns.Add("HSNCode", typeof(string));
                    dt.Columns.Add("TaxCode", typeof(string));
                    dt.Columns.Add("Amount", typeof(double));
                    dt.Columns.Add("TaxAmount", typeof(double));
                    dt.Columns.Add("TaxItem", typeof(string));
                    dt.Columns.Add("TaxItemPercentage", typeof(double));
                    dt.Columns.Add("TaxItemAmount", typeof(double));
                }
                //fill hsn code wise tax details in dt
                foreach (invoiceoutdetail iod in IODetails)
                {
                    string tstr = iod.TaxDetails;
                    string[] lst1 = tstr.Split('\n');
                    for (int j = 0; j < lst1.Length - 1; j++)
                    {
                        string[] lst2 = lst1[j].Split('-');
                        if (Convert.ToDouble(lst2[1]) > 0)
                        {
                            dt.Rows.Add();
                            dt.Rows[dt.Rows.Count - 1][0] = iod.HSNCode.Substring(0, HSNLength);
                            dt.Rows[dt.Rows.Count - 1][1] = iod.TaxCode;
                            dt.Rows[dt.Rows.Count - 1][2] = iod.Quantity * iod.Price;
                            dt.Rows[dt.Rows.Count - 1][3] = iod.Tax;
                            dt.Rows[dt.Rows.Count - 1][4] = lst2[0];
                            dt.Rows[dt.Rows.Count - 1][5] = iod.HSNCode; //need to replace with percentage
                            dt.Rows[dt.Rows.Count - 1][6] = lst2[1];
                        }
                    }

                }
            }
            catch (Exception ex)
            {
            }

            try
            {
                //fill tax rate for each tax item in dt
                TaxCodeWorkingDB tcwdb = new TaxCodeWorkingDB();
                List<taxcodeworking> tcwDetails = tcwdb.getTaxCodeDetails();
                for (int i = 0; i < (dt.Rows.Count); i++)
                {
                    foreach (taxcodeworking tcwd in tcwDetails)
                    {
                        if (dt.Rows[i][1].ToString() == tcwd.TaxCode && dt.Rows[i][4].ToString() == tcwd.TaxItemName)
                        {
                            dt.Rows[i][5] = tcwd.OperatorValue;
                            break;
                        }
                    }

                }
                //prepare HSN Code wise totals in a new table
                System.Data.DataTable dttotal = new System.Data.DataTable();
                dttotal = dt.Copy();
                dttotal.Clear();

                for (int i = 0; i < (dt.Rows.Count); i++)
                {

                    Boolean fount = false;
                    string tstr1 = dt.Rows[i][0].ToString();
                    string tstr2 = dt.Rows[i][4].ToString();
                    string tstr3 = dt.Rows[i][5].ToString();
                    for (int j = 0; j < (dttotal.Rows.Count); j++)
                    {
                        string tstr4 = dttotal.Rows[j][0].ToString();
                        string tstr5 = dttotal.Rows[j][4].ToString();
                        string tstr6 = dttotal.Rows[j][5].ToString();

                        if (tstr1 == tstr4 && tstr2 == tstr5 && tstr3 == tstr6)
                        {
                            dttotal.Rows[j][2] = Convert.ToDouble(dttotal.Rows[j][2].ToString()) +
                                Convert.ToDouble(dt.Rows[i][2].ToString());
                            dttotal.Rows[j][6] = Convert.ToDouble(dttotal.Rows[j][6].ToString()) +
                                Convert.ToDouble(dt.Rows[i][6].ToString());
                            fount = true;
                        }
                    }
                    if (!fount)
                    {
                        dttotal.ImportRow(dt.Rows[i]);
                    }
                }
                string tstr = "";
                ////for (int i = 0; i < (dttotal.Rows.Count); i++)
                ////{
                ////    tstr = tstr+
                ////        dttotal.Rows[i][0].ToString() + "," +
                ////        dttotal.Rows[i][1].ToString() + "," +
                ////        dttotal.Rows[i][2].ToString() + "," +
                ////        dttotal.Rows[i][3].ToString() + "," +
                ////        dttotal.Rows[i][4].ToString() + "," +
                ////        dttotal.Rows[i][5].ToString() + "," +
                ////        dttotal.Rows[i][6].ToString() + "\n";

                ////}
                ////MessageBox.Show(tstr);
                //create print table
                tstr = "";
                //find distinct tax item in dttotal
                DataTable dtDistinct = dttotal.AsEnumerable().GroupBy(row => row.Field<string>("TaxItem")).Select(group => group.First()).CopyToDataTable();
                ////for (int i = 0; i < (dtDistinct.Rows.Count); i++)
                ////{
                ////    tstr = tstr +
                ////        dtDistinct.Rows[i][0].ToString() + "," +
                ////        dtDistinct.Rows[i][1].ToString() + "," +
                ////        dtDistinct.Rows[i][2].ToString() + "," +
                ////        dtDistinct.Rows[i][3].ToString() + "," +
                ////        dtDistinct.Rows[i][4].ToString() + "," +
                ////        dtDistinct.Rows[i][5].ToString() + "," +
                ////        dtDistinct.Rows[i][6].ToString() + "\n";

                ////}
                ////MessageBox.Show(tstr);

                //create columns in dttax table. dynamically creating the columns for each tax item
                {
                    dtTax.Columns.Add("HSNCode", typeof(string));
                    dtTax.Columns.Add("Amount", typeof(double));
                    for (int i = 0; i < dtDistinct.Rows.Count && i < 3; i++)
                    {
                        dtTax.Columns.Add(dtDistinct.Rows[i][4].ToString(), typeof(string));
                        dtTax.Columns.Add(dtDistinct.Rows[i][4].ToString() + "Amount", typeof(double));
                    }
                    dtTax.Columns.Add("Total", typeof(double));
                }
                //add data in dttax table
                for (int i = 0; i < (dttotal.Rows.Count); i++)
                {
                    Boolean hsnFount = false;
                    string tstr1 = dttotal.Rows[i][0].ToString(); //for domestic
                    int j = 0;
                    for (j = 0; j < (dtTax.Rows.Count); j++)
                    {
                        string tstr2 = dtTax.Rows[j][0].ToString(); //for domestic
                        if (tstr1 == tstr2)
                        {
                            hsnFount = true;
                            break;
                        }
                    }
                    if (!hsnFount)
                    {
                        dtTax.Rows.Add();
                        j = dtTax.Rows.Count - 1;
                        dtTax.Rows[j][0] = tstr1;
                        dtTax.Rows[j][1] = dttotal.Rows[i][2]; ;
                    }
                    string tstr3 = dttotal.Rows[i][4].ToString();
                    string tstr4 = dttotal.Rows[i][4].ToString() + "Amount";
                    try
                    {
                        dtTax.Rows[j][tstr3] = dttotal.Rows[i][5];
                        dtTax.Rows[j][tstr4] = dttotal.Rows[i][6];
                        string t1 = String.IsNullOrEmpty(dtTax.Rows[j]["Total"].ToString()) ? "0" : dtTax.Rows[j]["Total"].ToString();
                        string t2 = String.IsNullOrEmpty(dttotal.Rows[i][6].ToString()) ? "0" : dttotal.Rows[i][6].ToString();
                        double d1 = Convert.ToDouble(t1) + Convert.ToDouble(t2);
                        dtTax.Rows[j]["Total"] = d1;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error creating HSN wise tax summary");
                    }
                }
                tstr = "";
                ////double td1=0, td2=0, td3 = 0;
                ////for (int i = 0; i < (dtTax.Rows.Count); i++)
                ////{
                ////    for (int j=0; j<dtTax.Columns.Count;j++)
                ////    {
                ////        tstr = tstr + dtTax.Rows[i][j].ToString() + ",";
                ////    }
                ////    tstr = tstr+"\n";
                ////    td1 = td1 + Convert.ToDouble(dtTax.Rows[i][1].ToString());
                ////    td2 = td2 + Convert.ToDouble(dtTax.Rows[i][dtTax.Columns.Count-1].ToString());
                ////}

                ////MessageBox.Show(tstr);
                ////MessageBox.Show(td1.ToString());
                ////MessageBox.Show(td2.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show("taxDetails4Print() : Error - " + ex.ToString());
            }
            return dtTax;
        }
        //new by justin li
        private ArrayList CreateXml(string tableName, string path)
        {
            string    xmlPath     = "";
            ArrayList xmlPathlist = new ArrayList();

            System.Data.DataTable dt = null;
            string sheetName         = "";

            switch (tableName)
            {
            case Constants.AccountTable:
                dt        = InitializeAccountDataTable();
                sheetName = "Customer Master";
                break;

            case Constants.OrderTable:
                dt        = InitializeOrderDataTable();
                sheetName = "Weekly Data";
                break;

            case Constants.MtlsPriceTable:
                dt        = InitializeMaterialPriceDataTable();
                sheetName = "Material Price";
                break;

            case Constants.OpenOrderTable:
                dt        = InitializeOpenOrderDataTable();
                sheetName = "Open Order";
                break;

            case Constants.ForeignOrderTable:
                dt        = InitializeForeignOrderDataTable();
                sheetName = "Data APAC";
                break;

            case Constants.ExchangeRatesTable:
                dt        = InitializeExchangeRatesDataTable();
                sheetName = "Exchange Rates";
                break;

            default:
                dt = null;
                break;
            }
            if (dt != null)
            {
                //'Item' is tagName in xml
                dt.TableName = "Item";
            }

            InitializeWorkbook(path);
            ISheet sheet = xssfworkbook.GetSheet(sheetName);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            if (dt == null)
            {
                return(null);
            }
            //start at line 2
            rows.MoveNext();
            int count = 0;
            int index = 0;

            while (rows.MoveNext())
            {
                if (count >= Constants.RowLimit)
                {
                    count   = 0;
                    xmlPath = CreateXml(tableName, index, dt);
                    //xmlPath = @"E:\Upload BLF_Data\" + tableName + DateTime.Now.ToString("yyyyMMddhhmmss") +"_"+ index + ".xml";
                    //create xml file to local
                    //dt.WriteXml(xmlPath);
                    xmlPathlist.Add(xmlPath);
                    dt.Clear();
                    index++;
                }


                IRow    row = (XSSFRow)rows.Current;
                DataRow dr  = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        //var evaluator = xssfworkbook.GetCreationHelper().CreateFormulaEvaluator();
                        switch (cell.CellType)
                        {
                        case CellType.Numeric:
                            dr[i] = cell.NumericCellValue.ToString();
                            break;

                        case CellType.Boolean:
                            dr[i] = cell.BooleanCellValue.ToString();
                            break;

                        case CellType.String:
                            dr[i] = cell.StringCellValue.ToString();
                            break;

                        case CellType.Error:
                            dr[i] = cell.ErrorCellValue.ToString();
                            break;

                        case CellType.Blank:
                            dr[i] = cell.ToString();
                            break;

                        case CellType.Formula:
                            // dr[i] = evaluator.EvaluateFormulaCell(cell);
                            throw new Exception(string.Format("formula cell at cell {0}", i + 1));

                        default:
                            dr[i] = cell.ToString();
                            break;
                        }
                    }
                }
                if (dr[0].ToString() != "")
                {
                    dt.Rows.Add(dr);
                }
                count++;
            }
            xmlPath = CreateXml(tableName, index, dt);
            //xmlPath = @"E:\Upload BLF_Data\" + tableName + DateTime.Now.ToString("yyyyMMddhhmmss") +"_"+ index + ".xml";
            //create xml file to local
            //dt.WriteXml(xmlPath);
            xmlPathlist.Add(xmlPath);
            dt.Dispose();


            return(xmlPathlist);
        }