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; } } }
/// <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(); }
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(); }
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(); } } } }
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"); } }
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; } } }
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 ; }
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; }
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; }
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"; }
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(); } }
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()); } }
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(); }
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(); } } } }
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(); } }
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; }
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(); }
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(); } }
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(); } }
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; } }
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; }
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()); } } }
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); } }
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); } }
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; }
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(); }
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")); }
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(); } }
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. Запрос одного параметра с названием ПЛК(ОТДЕЛЬНО ПОДУМОЙ!) //ПОКА БЕЗ ЭТОГО ПРОБУЕМ, ПОТОМ ПРИКРУТИМ }
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(); } } }
/// <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; }
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(); }
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"); }
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(); }
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(); }
//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.."); } }
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()); } }
//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"); } }
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); }