private void button1_Click(object sender, EventArgs e)
{
try
{
MySqlConnection myConn = new MySqlConnection();
myConn.Host = "192.168.69.8";
myConn.Port = 3306;
myConn.UserId = "cody";
myConn.Password = "******";
myConn.Open();
MySqlCommand myCommand = new MySqlCommand(" SELECT * FROM registration.regUser ;", myConn);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
myDataAdapter.SelectCommand = myCommand;
DataTable dbDataSet = new DataTable();
myDataAdapter.Fill(dbDataSet);
BindingSource bSource = new BindingSource();
bSource.DataSource = dbDataSet;
dataGridView1.DataSource = bSource;
myDataAdapter.Update(dbDataSet);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void Test(IMyMetaTestContext criteria)
{
IMyMetaPlugin plugin = null;
try
{
plugin = dbRoot.Plugins[criteria.ProviderType] as IMyMetaPlugin;
IMyMetaPluginContext context = new MyMetaPluginContext(plugin.ProviderUniqueKey, criteria.ConnectionString);
plugin.Initialize(context);
using (IDbConnection conn = plugin.NewConnection)
{
conn.Open();
conn.Close();
}
criteria.AppendLog("Connection Test Successful.");
}
catch (Exception ex)
{
criteria.AppendLog("Error testing connection", ex);
}
_databases = null;
TestDatabases(criteria, plugin);
TestTables(criteria, plugin);
TestViews(criteria, plugin);
TestProcedures(criteria, plugin);
_databases = null;
}
public static DataTable Sehirler()
{
try
{
using (baglan = new SqlConnection(ConnectionString))
{
baglan.Open();
using (komutver = new SqlCommand())
{
komutver.Connection = baglan;
komutver.CommandText = "sehir_Listele";
komutver.CommandType = CommandType.StoredProcedure;
adp = new SqlDataAdapter(komutver);
dt = new DataTable();
adp.Fill(dt);
}
}
}
catch (Exception)
{
throw;
}
return dt;
}
private void BindToColumns(DataTable metaData)
{
if(false == _fieldsBound)
{
if(metaData.Columns.Contains("TABLE_CATALOG")) f_Catalog = metaData.Columns["TABLE_CATALOG"];
if(metaData.Columns.Contains("TABLE_SCHEMA")) f_Schema = metaData.Columns["TABLE_SCHEMA"];
if(metaData.Columns.Contains("TABLE_NAME")) f_TableName = metaData.Columns["TABLE_NAME"];
if(metaData.Columns.Contains("INDEX_CATALOG")) f_IndexCatalog = metaData.Columns["INDEX_CATALOG"];
if(metaData.Columns.Contains("INDEX_SCHEMA")) f_IndexSchema = metaData.Columns["INDEX_SCHEMA"];
if(metaData.Columns.Contains("INDEX_NAME")) f_IndexName = metaData.Columns["INDEX_NAME"];
if(metaData.Columns.Contains("UNIQUE")) f_Unique = metaData.Columns["UNIQUE"];
if(metaData.Columns.Contains("CLUSTERED")) f_Clustered = metaData.Columns["CLUSTERED"];
if(metaData.Columns.Contains("TYPE")) f_Type = metaData.Columns["TYPE"];
if(metaData.Columns.Contains("FILL_FACTOR")) f_FillFactor = metaData.Columns["FILL_FACTOR"];
if(metaData.Columns.Contains("INITIAL_SIZE")) f_InitializeSize = metaData.Columns["INITIAL_SIZE"];
if(metaData.Columns.Contains("NULLS")) f_Nulls = metaData.Columns["NULLS"];
if(metaData.Columns.Contains("SORT_BOOKMARKS")) f_SortBookmarks = metaData.Columns["SORT_BOOKMARKS"];
if(metaData.Columns.Contains("AUTO_UPDATE")) f_AutoUpdate = metaData.Columns["AUTO_UPDATE"];
if(metaData.Columns.Contains("NULL_COLLATION")) f_NullCollation = metaData.Columns["NULL_COLLATION"];
if(metaData.Columns.Contains("COLLATION")) f_Collation = metaData.Columns["COLLATION"];
if(metaData.Columns.Contains("CARDINALITY")) f_Cardinality = metaData.Columns["CARDINALITY"];
if(metaData.Columns.Contains("PAGES")) f_Pages = metaData.Columns["PAGES"];
if(metaData.Columns.Contains("FILTER_CONDITION")) f_FilterCondition = metaData.Columns["FILTER_CONDITION"];
if(metaData.Columns.Contains("INTEGRATED")) f_Integrated = metaData.Columns["INTEGRATED"];
}
}
void ConvertToDataTable()
{
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < 5; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());
}
while (rows.MoveNext())
{
IRow row = (HSSFRow)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
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
dataSet1.Tables.Add(dt);
}
public void LoadData()
{
GridTable = new DataTable("FalseX2011-07");
GridTable = MyCL.LoadDataTable("Select knowID, know From CDknow");
gridControlMain.DataSource = GridTable;
gridControlMain.RefreshDataSource();
}
public void ExcuteSQLBulkCopy(DataTable Datatable, string TableName, ConnectionState connectionstate, ref bool executionSucceeded)
{
try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
SqlBulkCopy.BatchSize = Datatable.Rows.Count;
SqlBulkCopy.BulkCopyTimeout = 0;
SqlBulkCopy.DestinationTableName = TableName;
SqlBulkCopy.WriteToServer(Datatable);
SqlBulkCopy.Close();
executionSucceeded = true;
}
catch (Exception ex)
{
executionSucceeded = false;
HandleExceptions(ex);
}
finally
{
//SqlBulkCopy.ColumnMappings.Clear();
if (connectionstate == ConnectionState.CloseOnExit)
{
if (objConnection.State == System.Data.ConnectionState.Open)
{
objConnection.Close();
}
}
}
}
private void MenuFileOpen_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
string filename = openFileDialog.FileName;
string extension = Path.GetExtension(filename);
if (extension == ".xls" || extension == ".xlsx")
{
ExcelReader db = new ExcelReader(filename, true, false);
TableSelectDialog t = new TableSelectDialog(db.GetWorksheetList());
if (t.ShowDialog(this) == DialogResult.OK)
{
this.sourceTable = db.GetWorksheet(t.Selection);
this.dgvAnalysisSource.DataSource = sourceTable;
this.comboBox1.Items.Clear();
this.checkedListBox1.Items.Clear();
foreach (DataColumn col in sourceTable.Columns)
{
this.comboBox1.Items.Add(col.ColumnName);
this.checkedListBox1.Items.Add(col.ColumnName);
}
this.comboBox1.SelectedIndex = 0;
}
}
}
}
/// <summary>
/// �����ݼ�������Ϊexcel
/// </summary>
/// <param name="name">����excel������</param>
/// <param name="ds">�����������ݼ�</param>
public static void AddExcel(string name, DataTable dt)
{
string fileName = name + ".xls";
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
int rowIndex = 1;
int colIndex = 0;
excel.Application.Workbooks.Add(true);
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
}
excel.Visible = false;
excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
excel.Quit();
excel = null;
GC.Collect();//��������
}
private void BindGridView()
{
CommonDao<Safezhuan> newmgr = new CommonDao<Safezhuan>();
DataTable dt = new DataTable();
dt = newmgr.FindDataTableByCondition("");
if (dt.Rows.Count == 0)
{
dt.Rows.Add(dt.NewRow());
GridViewEmployee.DataSource = dt;
GridViewEmployee.DataBind();
int columnCount = dt.Columns.Count;
GridViewEmployee.Rows[0].Cells.Clear();
GridViewEmployee.Rows[0].Cells.Add(new TableCell());
GridViewEmployee.Rows[0].Cells[0].ColumnSpan = columnCount + 1;
GridViewEmployee.Rows[0].Cells[0].Text = "没有记录";
GridViewEmployee.Rows[0].Cells[0].Style.Add("text-align", "center");
}
else
{
GridViewEmployee.DataSource = dt;
GridViewEmployee.DataBind();
}
}
public DataTable ObtenerDatos(String ComandoSql)
{
SqlDataAdapter adapter;
DataTable dt = new DataTable();
try
{
con.Open();
Cmd.Connection = con;
Cmd.CommandText = ComandoSql;
adapter = new SqlDataAdapter(Cmd);
adapter.Fill(dt);
}
catch (Exception ex)
{
throw ex;
//MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
return dt;
}
/// <summary>
/// Get InvoiceNo Corresponding to Ledger For PurchaseReturn Register
/// </summary>
/// <param name="decLedgerId"></param>
/// <returns></returns>
public List<DataTable> GetInvoiceNoCorrespondingtoLedgerForPurchaseReturnReport(decimal decLedgerId, decimal decVoucherId)
{
List<DataTable> ListObj = new List<DataTable>();
DataTable dtbl = new DataTable();
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlDataAdapter sqldataadapter = new SqlDataAdapter("GetInvoiceNoCorrespondingtoLedgerForPurchaseReturnReport", sqlcon);
sqldataadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter sqlparameter = new SqlParameter();
sqlparameter = sqldataadapter.SelectCommand.Parameters.Add("@ledgerId", SqlDbType.Decimal);
sqlparameter.Value = decLedgerId;
sqlparameter = sqldataadapter.SelectCommand.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
sqlparameter.Value = decVoucherId;
sqldataadapter.Fill(dtbl);
ListObj.Add(dtbl);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
sqlcon.Close();
}
return ListObj;
}
/// <summary>
/// 生成DAL带命名空间
/// </summary>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <param name="strNamespace"></param>
/// <returns></returns>
public StringBuilder CreateDALCode(string tableName, DataTable dt, string strNamespace)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("using "+ strNamespace + ".IDAL.Bases;");
sb.AppendLine("using " + strNamespace + ".DAL.Bases;");
sb.AppendLine("using "+ strNamespace + ".Models;");
sb.AppendLine("using System.Collections.Generic;");
sb.AppendLine("using System;");
sb.AppendLine("using System.Data;");
string sqluse = sqltype == 2 ? "using MySql.Data.MySqlClient;" : "using System.Data.SqlClient;";
sb.AppendLine(sqluse);
sb.AppendLine("");
sb.AppendLine("namespace " + strNamespace + ".DAL {");
string cName = ":MsSqlDALConf";
if (sqltype == (int)SQLType.MySql)
{
cName = ":MySqlDALConf";
}
sb.AppendLine("public class " + tableName + "DAL " + cName + ", I"+ tableName + "DAL{");
sb.AppendLine("");
sb.AppendLine(" public "+ tableName + "DAL() : base() { }");
sb.AppendLine(" public " + tableName + "DAL(string name) : base(name) { }");
GetAllDAL(tableName, dt, sb, " ");
sb.AppendLine(" }");
sb.AppendLine("}");
return sb;
}
public void GetReady ()
{
dataTable = new DataTable ("itemTable");
dc1 = new DataColumn ("itemId");
dc2 = new DataColumn ("itemName");
dc3 = new DataColumn ("itemPrice");
dc4 = new DataColumn ("itemCategory");
dataTable.Columns.Add (dc1);
dataTable.Columns.Add (dc2);
dataTable.Columns.Add (dc3);
dataTable.Columns.Add (dc4);
DataRow dr;
seed = 123;
rowCount = 5;
rndm = new Random (seed);
for (int i = 1; i <= rowCount; i++) {
dr = dataTable.NewRow ();
dr["itemId"] = "item " + i;
dr["itemName"] = "name " + rndm.Next ();
dr["itemPrice"] = "Rs. " + (rndm.Next () % 1000);
dr["itemCategory"] = "Cat " + ((rndm.Next () % 10) + 1);
dataTable.Rows.Add (dr);
}
dataTable.AcceptChanges ();
dataView = new DataView (dataTable);
dataView.ListChanged += new ListChangedEventHandler (OnListChanged);
listChangedArgs = null;
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack) return;
var datos = new DataTable();
datos.Columns.Add("Nombre");
datos.Columns.Add("Activo");
var row = datos.NewRow();
row["Nombre"] = "Palma";
row["Activo"] = "Si";
datos.Rows.Add(row);
row = datos.NewRow();
row["Nombre"] = "Caucho";
row["Activo"] = "Si";
datos.Rows.Add(row);
row = datos.NewRow();
row["Nombre"] = "Café";
row["Activo"] = "Si";
datos.Rows.Add(row);
ViewState["Datos"] = datos;
ViewState["ckbActivarFiltro"] = "";
}
protected void Page_Load(object sender, EventArgs e)
{
// if (ConfigurationManager.AppSettings["ISONHOLIDAY"] == "Yes")
var menuItems = (from all in context.Items select all);
if (!IsPostBack)
{
DataTable table = new DataTable();
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Ingredients", typeof(string));
table.Columns.Add("Price", typeof(decimal));
DataRow row;
foreach (var item in menuItems)
{
row = table.NewRow();
row["Name"] = item.Name;
row["Ingredients"] = item.Ingredients;
row["Price"] = item.Price.ToString("#0.00");
table.Rows.Add(row);
}
MainMenuGrid.DataSource = table;
MainMenuGrid.DataBind();
}
}
public static string UpdateSagaXMLFile(ref DataTable _XMLDt, string XMLpath)
{
try
{
XmlDocument xmldoc = new XmlDocument();
xmldoc.Load(XMLpath);
XmlNode xmlnode = xmldoc.DocumentElement.ChildNodes[0];
xmlnode["ODBCDriverName"].InnerText = _XMLDt.Rows[0]["ODBCDriverName"].ToString();
xmlnode["HostName"].InnerText = _XMLDt.Rows[0]["HostName"].ToString();
xmlnode["ServerName"].InnerText = _XMLDt.Rows[0]["ServerName"].ToString();
xmlnode["ServiceName"].InnerText = _XMLDt.Rows[0]["ServiceName"].ToString();
xmlnode["Protocol"].InnerText = _XMLDt.Rows[0]["Protocol"].ToString();
xmlnode["DatabaseName"].InnerText = _XMLDt.Rows[0]["DatabaseName"].ToString();
xmlnode["UserId"].InnerText = _XMLDt.Rows[0]["UserId"].ToString();
xmlnode["Password"].InnerText = _XMLDt.Rows[0]["Password"].ToString();
xmlnode["ClientLocale"].InnerText = _XMLDt.Rows[0]["ClientLocale"].ToString();
xmlnode["DatabaseLocale"].InnerText = _XMLDt.Rows[0]["DatabaseLocale"].ToString();
xmldoc.Save(XMLpath);
return "";
}
catch (Exception err)
{
return err.Message;
}
}
/// <summary>
/// constructor optionally calls method to init the row information structure
/// and return the itself - singleton
/// </summary>
/// <param name="dt">table to gather info on</param>
/// <param name="init">if true, initialize; for example, on import</param>
/// <returns></returns>
public static dtRowInformation getdtRI(DataTable dt, bool init)
{
//pass null after initialization to access the DTRowInfo property
//or pass init=true (after import) to initialize
if (dtRI == null || init) dtRI = new dtRowInformation(dt);
return dtRI;
}
public static DataTable GetAllCidades(int estado_id)
{
DataTable retorno = new DataTable();
StringBuilder SQL = new StringBuilder();
SQL.Append(@"SELECT CidadeId, Nome FROM Cidade WHERE EstadoId = @ESTADO_ID");
try
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mendes_varejo"].ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL.ToString(), connection);
command.Parameters.AddWithValue("@ESTADO_ID", estado_id);
command.ExecuteNonQuery();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(retorno);
}
}
catch (Exception erro)
{
throw erro;
}
return retorno;
}
public void Write(DataTable table)
{
Log.Debug(String.Format("Writing {0} {1} to database..", table.Rows.Count, "record".Pluralize(table.Rows.Count)));
// Reopen connection, if it has closed for some reason.
if (!Adapter.IsConnectionOpen())
{
Adapter.OpenConnection();
}
// Manage dynamic table creation/management, if requested.
if (!isInitialized)
{
DbTableManager.InitializeTable(Adapter, table, tableInitializationOptions);
isInitialized = true;
}
// Write all rows in table.
int numRecordsWritten = 0;
foreach (DataRow row in table.Rows)
{
try
{
Adapter.InsertRow(table.TableName, row);
numRecordsWritten++;
}
catch (DbException) { }
}
Log.Debug(String.Format("Finished writing {0} {1}!", numRecordsWritten, "record".Pluralize(numRecordsWritten)));
}
public string InsertKhachHang(string TenKH, string Gioitinh, string Diachi, string SDT, string LoaiKH, string Ghichu)
{
string str = "";
string sql = "InsertKH";
// string sql = "insert NhomSP values (@Manhom, @Tennhom, @NgayCN)";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TenKH", TenKH);
cmd.Parameters.AddWithValue("@gt", Gioitinh);
cmd.Parameters.AddWithValue("@DC", Diachi);
cmd.Parameters.AddWithValue("@SDT", SDT);
cmd.Parameters.AddWithValue("@LoaiKH", LoaiKH);
cmd.Parameters.AddWithValue("@ghichu", Ghichu);
//cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
str = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
con.Close();
return str;
}
protected void Page_Load(object sender, EventArgs e)
{
#region Properties For The Base Class and Check Login
LoginID = Request.QueryString["LoginID"];
LoginInfo = CCLib.Login.GetLoginInfo(LoginID);
Redirect();
PageTitle = GetPortText(5372, false);
Section = "portfolio_tool";
CSS = "student";
HeadTag = "<STYLE TYPE='TEXT/CSS'><!--.btnDescription { font: 11px Verdana, Arial, Helvetica, sans-serif; margin: 0px 15px}--></STYLE>";
BodyTag = " alink='#990000' style='background-image:url(/media/shared/bg_left_bar_grey.gif);background-repeat:repeat-y;' bgcolor='#ffffff' leftmargin=0 link='#003366' text='#000000' topmargin=0 vlink='#003366' marginwidth='0' marginheight='0'";
LeftBar = "<TABLE WIDTH='100%' BORDER='0' CELLSPACING='0' CELLPADDING='0'><TR VALIGN='TOP' BGCOLOR='#669966'><td background='/media/portfolio_tool/bg_bar_tan_top.gif'><img height=23 src='/media/student_parent/icons/icon_arrow.gif' alt='' width=24><img height=23 src='/media1/" + this.PortType + "/h_h_main" + this.SuffixCode() + ".gif' alt='' width=141>";
#endregion Properties For The Base Class and Check Login
#region Initialize Values and Form Execution
CheckPortSession();
if (!Page.IsPostBack)
{
strSQL = "select c.SectionNameCapital" + SuffixCode() + " Ability,Convert(varchar(10),DBO.fn_ConvertTimeZone(StartTime," + PortInfo["SchoolID"].ToString() + ",1),101) [Test Date], Percentile,CorrectAnswers Correct,TotalQuestions,a.APTestID from AP_SavedTest a join AP_SavedSection b on a.APTestID = b.APTestID join AP_Section c on b.SectionID = c.SectionID where PortfolioID = " + PortInfo["PortfolioID"].ToString() + " and IsActiveStatus = 1 order by b.SectionID";
dtSummary = CCLib.Common.DataAccess.GetDataTable(strSQL);
// Prevent user manually change url from browser
if (dtSummary.Rows.Count == 0)
Response.Redirect("AP_Portfolio2.aspx?LoginID=" + LoginID);
if (dtSummary.Rows.Count > 0 && dtSummary.Rows.Count < 6)
Response.Redirect("AP_Portfolio3.aspx?LoginID=" + LoginID);
//Check if Matchmaker results deleted
divMM.Visible = false;
ClientStorage clientStorage = new ClientStorage();
clientStorage.Retrieve(LoginID);
if (clientStorage.AspectIndicator < 2)
{
divMM.Visible = true;
divLinkMM.Visible = false;
}
if (dtSummary.Rows[0]["Percentile"].ToString() == "")
{
UpdateScore();
strSQL = "select c.SectionNameCapital" + SuffixCode() + " Ability,Convert(varchar(10),DBO.fn_ConvertTimeZone(StartTime," + PortInfo["SchoolID"].ToString() + ",1),101) [Test Date], Percentile,CorrectAnswers Correct,TotalQuestions,a.APTestID from AP_SavedTest a join AP_SavedSection b on a.APTestID = b.APTestID join AP_Section c on b.SectionID = c.SectionID where PortfolioID = " + PortInfo["PortfolioID"].ToString() + " and SchoolID = " + PortInfo["SchoolID"].ToString() + " and IsActiveStatus = 1 order by b.SectionID";
dtSummary = CCLib.Common.DataAccess.GetDataTable(strSQL);
}
rptSummary.DataSource = dtSummary;
rptSummary.DataBind();
AssignToUserControl();
}
#endregion Initialize Values and Form Execution
#region Properties For The Individual Base Class
SetCustomJS();
PortTitle = GetPortText(5372,false);
SetTitleBar();
SetLeftButtons();
PortSubTitle = GetPortText(8804, false);
SetSubTitleBar();
SetFooter();
#endregion Properties For The Individual Base Class
}
private DataTable GetTiposVenda()
{
DataTable dataTable = new DataTable();
this.con = new SqlConnection(Security.GetCnn());
string selectCommandText = "";
if (this._dev != "D")
{
selectCommandText = "select tipo_venta_id, descripcion, devolucion from Tipo_venta where devolucion='N'";
}
else
{
selectCommandText = "select tipo_venta_id, descripcion, devolucion from Tipo_venta where devolucion='S'";
}
if ((this.con != null) && (this.con.State == ConnectionState.Open))
{
this.con.Close();
}
this.con.Open();
try
{
new SqlDataAdapter(selectCommandText, this.con).Fill(dataTable);
this.con.Close();
}
catch (Exception)
{
}
return dataTable;
}
internal static void SetDSInfo(DataTable dsTable) {
DataRow row = dsTable.NewRow();
row["TypeName"] = "SMALLINT";
row["ProviderDbType"] = MySqlDbType.Int16;
row["ColumnSize"] = 0;
row["CreateFormat"] = "SMALLINT";
row["CreateParameters"] = null;
row["DataType"] = "System.Int16";
row["IsAutoincrementable"] = true;
row["IsBestMatch"] = true;
row["IsCaseSensitive"] = false;
row["IsFixedLength"] = true;
row["IsFixedPrecisionScale"] = true;
row["IsLong"] = false;
row["IsNullable"] = true;
row["IsSearchable"] = true;
row["IsSearchableWithLike"] = false;
row["IsUnsigned"] = false;
row["MaximumScale"] = 0;
row["MinimumScale"] = 0;
row["IsConcurrencyType"] = DBNull.Value;
row["IsLiteralsSupported"] = false;
row["LiteralPrefix"] = null;
row["LiteralSuffix"] = null;
row["NativeDataType"] = null;
dsTable.Rows.Add(row);
}
private static DataTable GetDatabases(IMyMetaTestContext criteria, IMyMetaPlugin plugin)
{
if (_databases == null)
{
_databases = plugin.Databases;
if (criteria.DefaultDatabaseOnly)
{
List<DataRow> rowsToDelete = new List<DataRow>();
string defaultDb = plugin.DefaultDatabase;
if (!string.IsNullOrEmpty(defaultDb))
{
defaultDb = defaultDb.Trim();
foreach (DataRow dbRow in _databases.Rows)
{
string dbname = dbRow["CATALOG_NAME"].ToString();
if (dbname != defaultDb) rowsToDelete.Add(dbRow);
}
}
if (rowsToDelete.Count != (_databases.Rows.Count - 1))
{
rowsToDelete.Clear();
for (int i = 1; i < _databases.Rows.Count; i++) rowsToDelete.Add(_databases.Rows[i]);
}
foreach (DataRow dbRow in rowsToDelete) _databases.Rows.Remove(dbRow);
}
}
return _databases;
}
public static DataTable Ilceler(int id)
{
try
{
using (baglan = new SqlConnection(ConnectionString))
{
baglan.Open();
using (komutver = new SqlCommand())
{
komutver.Connection = baglan;
komutver.CommandText = "ilce_Listele";
komutver.Parameters.Add("@ilceId", SqlDbType.Int);
komutver.Parameters["@ilceId"].Value = Convert.ToInt32(id);
komutver.CommandType = CommandType.StoredProcedure;
adp = new SqlDataAdapter(komutver);
dt = new DataTable();
adp.Fill(dt);
}
}
}
catch (Exception)
{
throw;
}
return dt;
}
public static Task CreateTaskByTable(DataTable dt)
{
Task task = null;
try
{
task = new Task();
task.TaskNo = (dt.Rows[0]["TaskNo"] == null ? "" : dt.Rows[0]["TaskNo"].ToString());
task.BatchNo = (dt.Rows[0]["BatchNo"] == null ? "" : dt.Rows[0]["BatchNo"].ToString());
task.ItemNo = (dt.Rows[0]["ItemNo"] == null ? "" : dt.Rows[0]["ItemNo"].ToString());
task.ItemName = (dt.Rows[0]["ItemName"] == null ? "" : dt.Rows[0]["ItemName"].ToString());
task.Sn = (dt.Rows[0]["Sn"] == null ? "" : dt.Rows[0]["Sn"].ToString());
task.PV = (dt.Rows[0]["PV"] == null ? 0 : int.Parse(dt.Rows[0]["PV"].ToString()));
task.Result = (dt.Rows[0]["Result"] == null ? 0 : int.Parse(dt.Rows[0]["Result"].ToString()));
task.redundantVaue = (dt.Rows[0]["RedundantVaue"] == null ? 0 : int.Parse(dt.Rows[0]["RedundantVaue"].ToString()));
IFormatProvider ifp = new CultureInfo("zh-CN", true);
if (dt.Rows[0]["StartTime"] != null)
{
DateTime.TryParseExact(dt.Rows[0]["StartTime"].ToString(), DateTimeFormat2, ifp, DateTimeStyles.None, out task.StartTime);
}
if (dt.Rows[0]["CompleteTime"] != null)
{
DateTime.TryParseExact(dt.Rows[0]["CompleteTime"].ToString(), DateTimeFormat2, ifp, DateTimeStyles.None, out task.CompleteTime);
}
return task;
}
catch (Exception ex)
{
Program.WriteErrorLog("CreateTaskByTable", ex);
return task;
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public List<HisClient.Model.his_pm_import> DataTableToList(DataTable dt)
{
List<HisClient.Model.his_pm_import> modelList = new List<HisClient.Model.his_pm_import>();
int rowsCount = dt.Rows.Count;
if (rowsCount > 0)
{
HisClient.Model.his_pm_import model;
for (int n = 0; n < rowsCount; n++)
{
model = new HisClient.Model.his_pm_import();
model.ID= dt.Rows[n]["ID"].ToString();
model.SEND_DEPT_CODE= dt.Rows[n]["SEND_DEPT_CODE"].ToString();
model.RECEIVE_DEPT_CODE= dt.Rows[n]["RECEIVE_DEPT_CODE"].ToString();
model.PAKAGE_OUT_CODE= dt.Rows[n]["PAKAGE_OUT_CODE"].ToString();
model.CREATE_BY= dt.Rows[n]["CREATE_BY"].ToString();
if(dt.Rows[n]["CREATE_DATE"].ToString()!="")
{
model.CREATE_DATE=DateTime.Parse(dt.Rows[n]["CREATE_DATE"].ToString());
}
model.IMPORT_CODE= dt.Rows[n]["IMPORT_CODE"].ToString();
modelList.Add(model);
}
}
return modelList;
}
public void challanout()
{
DataTable dt = new DataTable("challandt");
dt = (DataTable)Session["challanout_dt"];
DataSet ds = new DataSet();
Viewer.Report.ChallanOutrpt crystalReport = new Report.ChallanOutrpt();
string st = HttpContext.Current.Server.MapPath("~/ChallanOutCrpt1.rpt");
// Label1.Text = st;
// Response.Write(string.Format("<script language='javascript' type='text/javascript'>alert( "+st+") </script>"));
crystalReport.Load(st);
if (ds.Tables.Contains("challandt") == true)
{
ds.Tables.Remove("challandt");
}
ds.Tables.Add(dt);
ds.WriteXmlSchema(HttpContext.Current.Server.MapPath("~/App_Data/Challanout.xml"));
crystalReport.SetDataSource(dt);
CrystalReportViewer1.ReportSource = crystalReport;
crystalReport.ExportToHttpResponse
(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, true, "CHALLANOUTSHEET");
}
private void cbxclub_DropDownOpened(object sender, EventArgs e)
{
try
{
if (cbxseason.SelectedItem == null || cbxzone.SelectedItem == null || cbxdivision.SelectedItem == null)
{
MessageBox.Show("Select Season, Zone, Division");
}
string strRetrieve = "";
strRetrieve = "select * from clubdetails";
// cbxclub.Items.Clear();
OleDbDataAdapter adpt = new OleDbDataAdapter(strRetrieve, Database.getConnection());
DataTable dt = new DataTable();
adpt.Fill(dt);
foreach (DataRowView dr in dt.DefaultView)
{
// List<Team> lstFilter = Database.GetEntityList<Team>( false, false,false,Database.getConnection(),RecordStatus true);
//cbxclub.Items.Add(dr["ClubName"]);
//cbxteam.Items.Refresh();
if (!cbxclub.Items.Contains(dr["ClubName"]))// For remove list duplicacy
{
cbxclub.Items.Add(dr["ClubName"]);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将Excel数据导入到List表链中
/// </summary>
/// <param name="xlstable">Excel文件名</param>
/// <param name="xlssheet">工作簿</param>
/// <returns></returns>
protected static bool ImportExcelToList(string ExcelFileName, string xlssheet)
{
try
{
//打开Excel表,并写入DataSet中。
//string xlsfile = ExcelFileName;
//string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + xlsfile
// + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + ExcelFileName
+ ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + xlssheet + "$]", conn);
odda.Fill(ds, "table1");
conn.Close();
//读取DataSet数据集
ltExcelTags.Clear();
////////////////////////////////////////////////////////////////
System.Data.DataTable dt = ds.Tables[0];
int nAll = dt.Rows.Count;
int nValid = 0;
foreach (System.Data.DataRow dr in dt.Rows)
{
if (dr["TagID"].ToString() != "")
{
WS_ExcelTag exlstag = new WS_ExcelTag();
exlstag.TagID = dr["TagID"].ToString().Trim();
exlstag.UnitCode = dr["UnitCode"].ToString().Trim();
exlstag.TagCode = dr["TagCode"].ToString().Trim();
exlstag.TagDesc = dr["TagDesc"].ToString().Trim();
exlstag.TagType = dr["TagType"].ToString().Trim();
exlstag.TagEngunit = dr["TagEngunit"].ToString().Trim();
exlstag.TagIsValid = dr["TagIsValid"].ToString().Trim();
exlstag.TagRunExp = dr["TagRunExp"].ToString().ToUpper().Trim();
exlstag.TagShiftExp = dr["TagShiftExp"].ToString().ToUpper().Trim();
exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim();
exlstag.TagMWExp = dr["TagMWExp"].ToString().ToUpper().Trim();
exlstag.TagSnapExp = dr["TagSnapExp"].ToString().ToUpper().Trim();
exlstag.TagTargetExp = dr["TagTargetExp"].ToString().ToUpper().Trim();
exlstag.TagScoreExp = dr["TagScoreExp"].ToString().ToUpper().Trim();
exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim();
//sub tag
ltExcelTags.Add(exlstag);
nValid += 1;
}
}
string strInfor = "Excel表总数为：{0}个, 【ID不为空】有效点为:{1}个。";
strInfor = string.Format(strInfor, nAll, nValid);
LogUtil.LogMessage(strInfor);
return(true);
}
catch (Exception ex)
{
LogUtil.LogMessage("Excel表读取失败:" + ex.ToString());
return(false);
}
}
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wk;
Microsoft.Office.Interop.Excel.Worksheet mysheet;
int temprow;
List <string> mygonghaolist = new List <string>();
Range myrng;
object[] myobj;
System.Data.DataTable mytb = new System.Data.DataTable();
if (app == null)
{
MessageBox.Show("Excel 没有正常打开。 ");
return;
}
app.DisplayAlerts = false;
AppSettingsReader myread = new AppSettingsReader();
string filedir;
try
{
filedir = myread.GetValue("directory", Type.GetType("System.String")).ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
app.Quit();
return;
}
wk = app.Workbooks.Open(filedir + "月销售利润表模板(内贸).xls");
//wk = app.Workbooks.Open(filedir + "成本结算模板.xls");
if (wk == null)
{
MessageBox.Show("模板文件没找到！");
return;
}
try
{
销售成本结算DAL dal1 = new 销售成本结算DAL();
SqlDataReader dr = dal1.get内贸利润表(mydate.Value);
myobj = new object[16];
mysheet = wk.Worksheets["内贸销售利润表"];
MessageBox.Show("开始");
if (dr.HasRows)
{
temprow = 5;
while (dr.Read())
{
dr.GetValues(myobj);
myrng = mysheet.Range[mysheet.Cells[temprow, 1], mysheet.Cells[temprow, 16]];
myrng.NumberFormatLocal = "0.00";
myrng.Value2 = myobj;
temprow++;
}
}
dr.Close();
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
string myfilename = saveFileDialog1.FileName;
wk.SaveAs(myfilename);
}
wk.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
app.Quit();
}
}
/// <summary>
/// DataTable转XML
/// </summary>
/// <param name="DT">DataTable</param>
/// <returns>XML</returns>
public static string ToXML(this Data.DataTable DT)
{
return(System.ClassTransform.DATATABLE.ToXML(DT));
}
private void btnSMaKH_Click(object sender, EventArgs e)
{
if (txtSMaKH.Text == "")
{
layDanhsach();
}
else
{
DataTable dt = new DataTable();
dt.Clear();
String sCommand = "";
if (cbKeHoach.Text != "")
{
sCommand = "select khachhang.hoten as hotenkh,chitietkhcs.* from khachhang,chitietkhcs where khachhang.makh=chitietkhcs.makh and chitietkhcs.ma='" + cbKeHoach.SelectedValue.ToString() + "'and khachhang.makh='" + txtSMaKH.Text + "'";
}
else
{
sCommand = "select khachhang.hoten as hotenkh,chitietkhcs.* from khachhang,chitietkhcs where khachhang.makh=chitietkhcs.makh and 1=0";
}
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();
DataColumn col = null;
col = new DataColumn("STT", typeof(int));
dskh.Columns.Add(col);
col = new DataColumn("Mã khách hàng", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Họ tên", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Nhận quà", typeof(bool));
dskh.Columns.Add(col);
col = new DataColumn("Ngày", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Nhân viên", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Edit", typeof(bool));
dskh.Columns.Add(col);
int iRows = dt.Rows.Count;
for (int i = 0; i < iRows; i++)
{
try
{
DataRow row = dskh.NewRow();
row[0] = i + 1;
row[1] = dt.Rows[i]["makh"].ToString();
row[2] = dt.Rows[i]["hotenkh"].ToString();
if (dt.Rows[i]["nhan"].ToString() == "True")
{
row[3] = true;
}
else
{
row[3] = false;
}
if (dt.Rows[i]["ngay"].ToString() != "")
{
row[4] = dt.Rows[i]["ngay"].ToString().Substring(0, 10);
}
row[5] = dt.Rows[i]["manv"].ToString();
row[6] = false;
dskh.Rows.Add(row);
}
catch { };
}
dgvDanhsach.DataSource = dskh;
dgvDanhsach.Columns[0].FillWeight = 30;
dgvDanhsach.Columns[0].Width = 20;
dgvDanhsach.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvDanhsach.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dgvDanhsach.Columns[0].ReadOnly = true;
dgvDanhsach.Columns[1].ReadOnly = true;
dgvDanhsach.Columns[2].ReadOnly = true;
dgvDanhsach.Columns[4].ReadOnly = true;
dgvDanhsach.Columns[5].ReadOnly = true;
dgvDanhsach.Columns[6].Visible = false;
}
}
protected void Page_Load(object sender, System.EventArgs e)
{
try
{
//display nav bar
Nav1.Feedback.Text = String.Empty;
Nav1.SideTabId = AssignmentManager.Common.constants.SIDE_NAV_COURSE_MANAGEMENT;
Nav1.TopTabId = AssignmentManager.Common.constants.TOP_NAV_COURSE_USERS;
Nav1.Title = SharedSupport.GetLocalizedString("AdminImport_Title");
Nav1.SubTitle = SharedSupport.GetLocalizedString("ImportForm_SubTitle");
Nav1.relativeURL = @"../";
GoBack1.GoBack_HelpUrl = SharedSupport.HelpRedirect("vstskAddingCourseUsers");
GoBack1.GoBackIncludeBack = true;
GoBack1.GoBack_left = "400px";
GoBack1.GoBack_top = "-15px";
GoBack1.GoBack_BackURL = "ImportForm.aspx?CourseID=" + Request.QueryString.Get("CourseID");
AssignmentManager.Common.Functions func = new AssignmentManager.Common.Functions();
courseId = func.ValidateNumericQueryStringParameter(this.Request, "CourseID");
if (courseId <= 0)
{
throw(new ArgumentException(SharedSupport.GetLocalizedString("Global_MissingParameter")));
}
if (!SharedSupport.SecurityIsAllowed(courseId, SecurityAction.USER_ADD))
{
// Note that Redirect ends page execution.
Response.Redirect(@"../Error.aspx?ErrorDetail=" + "Global_Unauthorized");
}
if (!IsPostBack)
{
//clear combos
cboEmailAddress.Items.Clear();
cboFirstName.Items.Clear();
cboLastName.Items.Clear();
cboMiddleName.Items.Clear();
cboUniversityID.Items.Clear();
cboUserName.Items.Clear();
}
//Localize all items
localizeLabels();
//grab items off of the querystring - DECODE them
uploadedFilePath = SharedSupport.AddBackSlashToDirectory(Server.MapPath(Constants.ASSIGNMENTMANAGER_UPLOAD_DIRECTORY)) + Server.UrlDecode(Request.QueryString.Get("File"));
delimitingCharacter = Server.UrlDecode(Request.QueryString.Get("Char"));
//Create an instance of the dataset
System.Data.DataSet ds = new System.Data.DataSet();
//populate the combo boxes with items from the delimited file
if (delimitingCharacter != "" && delimitingCharacter.Length < 2)
{
//Parse first line of file into dataset using delimiting character specified.
ds = SharedSupport.ParseDelimitedFile(uploadedFilePath, delimitingCharacter, 1);
//throw new System.IO.FileNotFoundException(SharedSupport.GetLocalizedString("User_UploadFileNotFound"));
}
else
{
throw(new ArgumentException(SharedSupport.GetLocalizedString("Global_MissingParameter")));
}
System.Data.DataSet dsNew = new System.Data.DataSet();
System.Data.DataTable dtNew = dsNew.Tables.Add();
dtNew.Columns.Add();
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
System.Data.DataRow drNew = dtNew.NewRow();
drNew[0] = ds.Tables[0].Rows[0][i].ToString();
dtNew.Rows.Add(drNew);
}
//populate combos
if (!Page.IsPostBack)
{
for (int i = 0; i < dsNew.Tables[0].Rows.Count; i++)
{
int dropdownIndex = i + 1;
ListItem newListItem = new ListItem(dsNew.Tables[0].Rows[i][0].ToString(), dropdownIndex.ToString());
cboEmailAddress.Items.Add(newListItem);
cboFirstName.Items.Add(newListItem);
cboLastName.Items.Add(newListItem);
cboMiddleName.Items.Add(newListItem);
cboUniversityID.Items.Add(newListItem);
cboUserName.Items.Add(newListItem);
}
}
txtImportedFileLocation.Text = uploadedFilePath;
}
catch (Exception ex)
{
Nav1.Feedback.Text = ex.Message.ToString();
}
}
public DataTable Load_treatments(string patient_id)
{
System.Data.DataTable dt_pt_main = db.table("SELECT id,date,dr_name FROM tbl_treatment_plan_main where pt_id='" + patient_id + "' ORDER BY date DESC");
return(dt_pt_main);
}
/// <summary>
/// 根据凭证号从SAP下载检验批信息
/// </summary>
public string execute261(string sPch)
{
string strTmpTable, strTmpField, strTmpOrder, strTmpWhere, strTmpMater;
CoreClientParam ccpData = new CoreClientParam();
ccpData.ServerName = "ygjzjl.base.QueryData";
ccpData.MethodName = "queryData";
strTmpTable = "DT_BOARDWEIGHTMAIN";
strTmpField = "FN_WEIGHT,FS_PRODUCTNO,FS_PLANT,FS_SAPSTORE,FS_ACCOUNTDATE";
strTmpWhere = " AND FS_STOVENO='" + sPch + "'";
strTmpOrder = "";
ccpData.ServerParams = new object[] { strTmpTable, strTmpField, strTmpWhere, strTmpOrder };
System.Data.DataTable dtTmpData = new System.Data.DataTable();
ccpData.SourceDataTable = dtTmpData;
try
{
this.ExecuteQueryToDataTable(ccpData, CoreInvokeType.Internal);
}
catch (Exception ex1)
{
return(ex1.ToString() + "信息查询异常！");
}
if (dtTmpData.Rows.Count == 0)
{
return("冶炼炉号" + sPch + "在计量系统不存在！");
}
string[] strTmpHeader = new string[] { "", "", "" };
ArrayList listSubItem = new ArrayList();
strTmpMater = getMaterial(dtTmpData.Rows[0]["FS_PRODUCTNO"].ToString());
strTmpHeader[0] = Convert.ToDateTime(dtTmpData.Rows[0]["FS_ACCOUNTDATE"].ToString()).ToString("yyyy.MM.dd");
strTmpHeader[1] = strTmpHeader[0];
strTmpHeader[2] = "";
listSubItem.Clear();
listSubItem.Add(strTmpMater); //物料编号
listSubItem.Add(dtTmpData.Rows[0]["FS_PLANT"].ToString()); //工厂
listSubItem.Add(dtTmpData.Rows[0]["FS_SAPSTORE"].ToString()); //库存地点
listSubItem.Add(sPch); //批次
listSubItem.Add("261"); //移动类型
listSubItem.Add(dtTmpData.Rows[0]["FN_WEIGHT"].ToString()); //发货数量
listSubItem.Add("TON"); //收货时的计量单位
listSubItem.Add(dtTmpData.Rows[0]["FS_PRODUCTNO"].ToString()); //生产订单编号
listSubItem.Add("0001"); //生产订单行项目号
string sTmp = BAPI_GOODSMVT_CREATE("03", strTmpHeader, listSubItem);
if (sTmp != "")
{
sTmp = "炉号" + sPch + "-凭证号" + sTmp + "-上传重量" + dtTmpData.Rows[0]["FN_WEIGHT"].ToString();
sTmp += "，发料成功！";
return(sTmp);
}
else
{
return("");
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
AppFunctions v = new AppFunctions();
if (Request.QueryString["Op"].ToLower() == "add")
{
if (string.IsNullOrEmpty(txtFirstName.Text))
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.error(\"الرجاء ادخال الاسم.\")</SCRIPT>", false);
return;
}
if (string.IsNullOrEmpty(txtUsername.Text))
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.error(\"الرجاء ادخال اسم المستخدم.\")</SCRIPT>", false);
return;
}
if (string.IsNullOrEmpty(txtEmail.Text))
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.error(\"الرجاء ادخال البريد الالكتروني.\")</SCRIPT>", false);
return;
}
if (string.IsNullOrEmpty(txtPassword.Text))
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.error(\"الرجاء ادخال كلمة السر.\")</SCRIPT>", false);
return;
}
}
if (!v.IsEmailValid(txtEmail.Text))
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.error(\"الرجاء التحقق من البريد الالكتوني.\")</SCRIPT>", false);
return;
}
if (txtPassword.Text.Length < 6)
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.error(\"كلمة السر يجب ان تكون بطول 6 احرف على الاقل\")</SCRIPT>", false);
return;
}
System.Data.DataTable dt = new System.Data.DataTable();
db.AddParameter("@username", txtUsername.Text);
db.AddParameter("@Email", txtEmail.Text);
if (Request.QueryString["Op"].ToLower().Equals("add"))
{
dt = db.ExecuteDataTable("Select * from AdminUsers Where UserName=@username Or Email=@Email");
}
else if (Request.QueryString["Op"].ToLower().Equals("edit"))
{
db.AddParameter("@id", Request.QueryString["id"]);
dt = db.ExecuteDataTable("Select * from AdminUsers Where (UserName=@username Or Email=@Email) and not id=@id");
}
if (dt.Rows.Count != 0)
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.error(\"خطأ : هذة البريد الالكتروني موجود مسبقا\")</SCRIPT>", false);
return;
}
db.AddParameter("@Name", txtFirstName.Text);
db.AddParameter("@UName", txtUsername.Text);
db.AddParameter("@Password", txtPassword.Text);
db.AddParameter("@Email", txtEmail.Text);
db.AddParameter("@permition", ddlPermition.SelectedValue);
if (Request.QueryString["Op"] == "Edit")
{
db.AddParameter("@id", Request.QueryString["id"]);
db.ExecuteNonQuery("update " + tablename + " set email=@Email,Name=@Name,UserName=@UName,Password=@Password,permition=@permition where id=@id");
//ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.success(\"Contact Added successfully.\")</SCRIPT>", false);
Response.Redirect("AdminList.aspx");
//ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.success(\"Contact Added successfully.\")</SCRIPT>", false);
}
else if (Request.QueryString["Op"] == "Add")
{
try
{
if (txtPassword.Text.Length <= 5)
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.error(\"Password Must Be 6 Char lenght at least\")</SCRIPT>", false);
return;
}
db.ExecuteNonQuery("insert into " + tablename + "(Name,UserName,Password,email,permition) Values(@Name,@UName,@Password,@Email,@permition)");
Response.Redirect("AdminList.aspx");
// ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.success(\"Contact Added successfully.\")</SCRIPT>", false);
}
catch
{
ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "WriteMsg", "<SCRIPT LANGUAGE=\"JavaScript\">alertify.error(\"Please try again.\")</SCRIPT>", false);
}
}
}
/// <summary>
/// Получение 10-ти значений.
/// </summary>
/// <param name="sql">Запрос SQL</param>
/// <param name="s1"></param>
/// <param name="s2"></param>
/// <param name="s3"></param>
/// <param name="s4"></param>
/// <param name="s5"></param>
/// <param name="s6"></param>
/// <param name="s7"></param>
/// <param name="s8"></param>
/// <param name="s9"></param>
/// <param name="s10"></param>
/// <returns>10 значений полученных запросом. Запрос должен возвращать одну строку</returns>
public bool GetValue10(string sql, out string s1, out string s2, out string s3, out string s4, out string s5, out string s6, out string s7, out string s8, out string s9, out string s10)
{
s1 = "";
s2 = "";
s3 = "";
s4 = "";
s5 = "";
s6 = "";
s7 = "";
s8 = "";
s9 = "";
s10 = "";
if ((sql == "") || (sql == "Empty"))
{
return(false);
}
var dt = new System.Data.DataTable();
SelectDT(sql, out dt);
try
{
//LastQueryDateTime = DateTime.Now;
if (dt.Rows.Count > 0)
{
if (dt.Columns.Count > 0)
{
s1 = dt.Rows[0][0].ToString();
}
if (dt.Columns.Count > 1)
{
s2 = dt.Rows[0][1].ToString();
}
if (dt.Columns.Count > 2)
{
s3 = dt.Rows[0][2].ToString();
}
if (dt.Columns.Count > 3)
{
s4 = dt.Rows[0][3].ToString();
}
if (dt.Columns.Count > 4)
{
s5 = dt.Rows[0][4].ToString();
}
if (dt.Columns.Count > 5)
{
s6 = dt.Rows[0][5].ToString();
}
if (dt.Columns.Count > 6)
{
s7 = dt.Rows[0][6].ToString();
}
if (dt.Columns.Count > 7)
{
s8 = dt.Rows[0][7].ToString();
}
if (dt.Columns.Count > 8)
{
s9 = dt.Rows[0][8].ToString();
}
if (dt.Columns.Count > 9)
{
s10 = dt.Rows[0][9].ToString();
}
return(true);
}
return(false);
}
catch (Exception e)
{
string errorMes = "702. Ошибка выполнения запроса: " + Var.CR + e.Message + Var.CR + sql;
//if (ErrorShow)
sys.SM(errorMes);
dt = null;
return(false);
}
}
private void layDS()
{
DataTable dt = new DataTable();
String sCommand = "";
sCommand = "SELECT * from Danso";
if (frmMain.conn.State == ConnectionState.Open)
{
frmMain.conn.Close();
}
frmMain.conn.Open();
new SqlDataAdapter(sCommand, frmMain.conn).Fill(dt);
frmMain.conn.Close();
System.Data.DataTable dskh = new System.Data.DataTable();
DataColumn col = null;
col = new DataColumn("STT", typeof(int));
dskh.Columns.Add(col);
col = new DataColumn("Huyện", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Phường xã", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Số dân", typeof(string));
dskh.Columns.Add(col);
int iRows = dt.Rows.Count;
for (int i = 0; i < iRows; i++)
{
try
{
DataRow row = dskh.NewRow();
row[0] = i + 1;
DataTable dt1 = new DataTable();
sCommand = "SELECT * from DMHuyen where MaHuyen='" + cbHuyen.SelectedValue.ToString() + "'";
if (frmMain.conn.State == ConnectionState.Open)
{
frmMain.conn.Close();
}
frmMain.conn.Open();
new SqlDataAdapter(sCommand, frmMain.conn).Fill(dt1);
frmMain.conn.Close();
row[1] = dt1.Rows[i]["TenHuyen"].ToString();
dt1.Clear();
sCommand = "SELECT * from DMXaPhuong where MaXa='" + cbXa.SelectedValue.ToString() + "'";
if (frmMain.conn.State == ConnectionState.Open)
{
frmMain.conn.Close();
}
frmMain.conn.Open();
new SqlDataAdapter(sCommand, frmMain.conn).Fill(dt1);
row[2] = dt1.Rows[i]["TenXa"].ToString();
dt1.Clear();
row[3] = dt.Rows[i]["Danso"].ToString();
dskh.Rows.Add(row);
}
catch { };
}
dgvDanhsach.DataSource = dskh;
dgvDanhsach.Columns[0].FillWeight = 20;
dgvDanhsach.Columns[0].Width = 20;
dgvDanhsach.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvDanhsach.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvDanhsach.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
protected void DiseaseDataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
string Data_Id = e.Command.Parameters["@IdReturn"].Value.ToString();
string txt2 = txtValues.Text;
string[] newlist = txt2.Split(new Char[] { ';', '\\' }, StringSplitOptions.RemoveEmptyEntries);
string[] IdSstring = new String[newlist.Length];
System.Data.DataTable dt = new System.Data.DataTable();
string bush = string.Empty;
SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
connection.Open();
DataTable dt3 = new DataTable();
SqlCommand sqlCmd3 = new SqlCommand("SELECT * from Symptoms ", connection);
// sqlCmd3.Parameters.AddWithValue("@username", user);
SqlDataAdapter sqlDa3 = new SqlDataAdapter(sqlCmd3);
sqlDa3.Fill(dt3);
int comcheck = 0;
int Incre = 0;
int DSincr = 0;
while (comcheck < newlist.Length)
{
for (int hu = 0; hu < dt3.Rows.Count; hu++)
{
if (newlist[comcheck] == dt3.Rows[hu]["Name"].ToString())
{
IdSstring[Incre] = dt3.Rows[hu]["Id"].ToString();
Incre++;
break;
}
}
comcheck++;
}
for (int q = 0; q < IdSstring.Length; q++)
{
string Sym_Check = "Symp" + q;
Disease_SymptomsDataSource.InsertParameters.Add(Sym_Check, newlist[q]);
Disease_SymptomsDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
Disease_SymptomsDataSource.InsertCommand = "INSERT INTO Disease_Symptoms(Disease_Id, Symptom_Id) VALUES(" + Data_Id + ", " + IdSstring[q] + ")";
Disease_SymptomsDataSource.Insert();
}
string txt = Aliases.Text;
string[] lst = txt.Split(new Char[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
//int a = lst.Length;
for (int a = 0; a < lst.Length; a++)
{
string check = "AliasName" + a;
AliasesDataSource.InsertParameters.Add(check, lst[a]);
AliasesDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
AliasesDataSource.InsertCommand = "INSERT INTO Aliases(Name, Disease_Id) VALUES(@" + check + ", " + Data_Id + ")";
AliasesDataSource.Insert();
}
CheckBoxList chkbx = (CheckBoxList)FindControl("CheckBoxList1");
for (int b = 0; b < 5; b++)
{
string check1 = "Vector_Id" + b;
if (CheckBoxList1.Items[b].Selected)
{
Disease_VectorsDataSource.InsertParameters.Add(check1, CheckBoxList1.Items[b].Value);
Disease_VectorsDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
Disease_VectorsDataSource.InsertCommand = "INSERT INTO Disease_Vectors(Disease_Id, Vector_Id) VALUES(" + Data_Id + ", @" + check1 + ")";
Disease_VectorsDataSource.Insert();
}
}
}
void ConvertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1)
{
if (!File.Exists(excelFilePath))
{
MessageBox.Show("Выберите файл в следующий раз");
}
if (File.Exists(csvOutputFile))
{
File.Delete(csvOutputFile);
}
// connection string
var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
var cnn = new OleDbConnection(cnnStr);
// get schema, then data
var dt = new System.Data.DataTable();
try
{
cnn.Open();
var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (schemaTable.Rows.Count < worksheetNumber)
{
throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
}
string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
string sql = String.Format("select * from [{0}]", worksheet);
var da = new OleDbDataAdapter(sql, cnn);
da.Fill(dt);
}
catch (Exception e)
{
// ???
throw e;
}
finally
{
// free resources
cnn.Close();
}
// write out CSV data
using (var wtr = new StreamWriter(csvOutputFile, false, Encoding.UTF8))
{
foreach (DataRow row in dt.Rows)
{
bool firstLine = true;
foreach (DataColumn col in dt.Columns)
{
if (!firstLine)
{
wtr.Write(",");
}
else
{
firstLine = false;
}
var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
wtr.Write(String.Format("\"{0}\"", data));
}
wtr.WriteLine();
}
}
}
public AccessUserDetails Details(Int64 UID)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "WHERE a.UID = @UID;";
cmd.Parameters.AddWithValue("UID", UID);
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
AccessUserDetails Details = new AccessUserDetails();
foreach (System.Data.DataRow dr in dt.Rows)
{
Details.UID = UID;
Details.UserName = "" + dr["UserName"].ToString();
Details.Password = "" + dr["Password"].ToString();
Details.DateCreated = DateTime.Parse(dr["DateCreated"].ToString());
Details.Deleted = bool.Parse(dr["Deleted"].ToString());
Details.Name = "" + dr["Name"].ToString();
Details.Address1 = "" + dr["Address1"].ToString();
Details.Address2 = "" + dr["Address2"].ToString();
Details.City = "" + dr["City"].ToString();
Details.State = "" + dr["State"].ToString();
Details.CountryID = Int32.Parse(dr["CountryID"].ToString());
Details.CountryName = "" + dr["CountryName"].ToString();
Details.OfficePhone = "" + dr["OfficePhone"].ToString();
Details.DirectPhone = "" + dr["DirectPhone"].ToString();
Details.HomePhone = "" + dr["HomePhone"].ToString();
Details.FaxPhone = "" + dr["FaxPhone"].ToString();
Details.MobilePhone = "" + dr["MobilePhone"].ToString();
Details.EmailAddress = "" + dr["EmailAddress"].ToString();
Details.GroupID = Int32.Parse(dr["GroupID"].ToString());
Details.GroupName = "" + dr["GroupName"].ToString();
Details.PageSize = Int32.Parse(dr["PageSize"].ToString());
Details.CreatedOn = DateTime.Parse(dr["CreatedOn"].ToString());
Details.LastModified = DateTime.Parse(dr["LastModified"].ToString());
}
return(Details);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
protected void btn_submit_Click(object sender, EventArgs e)
{
string connectionString = null;
MySqlConnection con;
connectionString = "server=localhost;database=mydb;Uid=root;Pwd=;";
con = new MySqlConnection(connectionString);
MySqlDataAdapter sdx = new MySqlDataAdapter("SELECT COUNT(*) FROM labbooking WHERE LabId='" + dropdown_lab_id.SelectedValue + "' AND BookingDate='" + dropdown_date.SelectedValue + "' AND BookingTime='" + dropdown_time.SelectedValue + "'", con);
System.Data.DataTable dt = new System.Data.DataTable();
sdx.Fill(dt);
if (dt.Rows[0][0].ToString() == "1")
{
System.Windows.MessageBox.Show("Lab already booked!");
return;
}
con.Close();
sdx = new MySqlDataAdapter("SELECT COUNT(*) FROM labbooking WHERE LecturerName='" + dropdown_lect_name.SelectedValue + "' AND BookingDate='" + dropdown_date.SelectedValue + "' AND BookingTime='" + dropdown_time.SelectedValue + "'", con);
dt = new System.Data.DataTable();
sdx.Fill(dt);
if (dt.Rows[0][0].ToString() == "1")
{
System.Windows.MessageBox.Show("Selected Lecturer is already booked!");
return;
}
con.Close();
sdx = new MySqlDataAdapter("SELECT COUNT(*) FROM labbooking WHERE LecturerName='" + dropdown_lect_name.SelectedValue + "' AND BookingDate='" + dropdown_date.SelectedValue + "' AND BookingTime='" + dropdown_time.SelectedValue + "'", con);
dt = new System.Data.DataTable();
sdx.Fill(dt);
if (dt.Rows[0][0].ToString() == "1")
{
System.Windows.MessageBox.Show("Selected Lecturer is already booked!");
return;
}
con.Close();
con.Open();
MySqlCommand sda = new MySqlCommand("SELECT MaxCapacity FROM lab where labId = '" + dropdown_lab_id.SelectedValue + "'", con);
MySqlDataReader dr;
dr = sda.ExecuteReader();
dr.Read();
int maxcap = Convert.ToInt32(dr["MaxCapacity"].ToString());
if (maxcap < Convert.ToInt32(txt_participants.Text))
{
System.Windows.MessageBox.Show("The participants have exceeded the maximum capacity!");
return;
}
con.Close();
DateTime date = Convert.ToDateTime(dropdown_date.SelectedValue);
string d = date.ToShortDateString();
con.Open();
string comm = "INSERT INTO labbooking (BookingDate, BookingTime, LabId, Participants, LecturerName, LecturerId, BatchId) VALUES ('" + d + "', '" + dropdown_time.SelectedValue + "', '" + dropdown_lab_id.SelectedValue + "', '" + txt_participants.Text + "', '" + dropdown_lect_name.SelectedValue + "', '" + lbl_lec_id.Text + "', '" + txt_batch_id.Text + "')";
sda = new MySqlCommand(comm, con);
sda.ExecuteNonQuery();
System.Windows.MessageBox.Show("DONE");
con.Close();
Response.Redirect("main.aspx");
}
public static void login()
{
//登录，当前如果已经登录，返回openid，如果没有登录，返回登录链接，重新登录
string gourl = "index.aspx";
if (!string.IsNullOrEmpty(HttpContext.Current.Request.QueryString["gourl"]))
{
gourl = HttpContext.Current.Request.QueryString["gourl"];
//if (HttpContext.Current.Request.Cookies["url"] == null)
//{
// HttpCookie hc = new HttpCookie("url");
// hc.Value = gourl;
// hc.Expires = DateTime.Now.AddDays(1);
// HttpContext.Current.Response.Cookies.Add(hc);
//}
//else
//{
// HttpCookie hc = HttpContext.Current.Request.Cookies["url"];
// hc.Value = gourl;
// hc.Expires = DateTime.Now.AddDays(1);
// HttpContext.Current.Response.Cookies.Add(hc);
//}
}
if (HttpContext.Current.Request.Cookies["mLoginCookies"] == null)
{
if (string.IsNullOrEmpty(HttpContext.Current.Request["openid"]))
{
//string url = "http://mp.qifanhui.cn/Api/Map/index.html?gourl=" + gourl;
string url = "http://mp.qifanhui.cn/Api/Map/index.html";
HttpContext.Current.Response.Redirect(url, true);
}
else
{
//微信授权登录返回，获取用户信息，记录到COOKIE
string openid = HttpContext.Current.Request["openid"];
//判断是否已经注册，未注册返回注册页面
string result = re.Get_Http("http://mp.qifanhui.cn/Api/Map/Fans/openid/" + openid + ".html", 3000);
weixin_info wx = LitJson.JsonMapper.ToObject <weixin_info>(result);
System.Data.SqlClient.SqlParameter[] sp = { new System.Data.SqlClient.SqlParameter("@openid", openid) };
System.Data.DataTable dt = Utility.SQLHelper.ExecuteTable("select member_id,member_realName from member where member_openid=@openid", sp);
string member_id = null;
if (dt.Rows.Count > 0)
{
//已经注册,获取用户信息
member_id = dt.Rows[0][0].ToString();
HttpCookie hc = new HttpCookie("mLoginCookies");
hc.Expires = DateTime.Now.AddDays(1);
hc.Values.Add("member_id", member_id);
HttpContext.Current.Response.AppendCookie(hc);
member mm = new member();
mm.memberName = wx.nickname;
mm.nickName = dt.Rows[0][1].ToString();
mm.headImgurl = wx.headimgurl;
HiFi.Common.CacheClass.SetCache("member_id" + member_id, mm, DateTime.Now.AddDays(1), TimeSpan.Zero);
//更新头像
dt = Utility.SQLHelper.ExecuteTable("select member_pic from member_info where member_id=" + member_id);
if (dt.Rows.Count > 0)
{
if (dt.Rows[0][0].ToString() != wx.headimgurl)
{
Utility.SQLHelper.ExecuteNonQuery("update member_info set member_pic='" + wx.headimgurl + "' where member_id=" + member_id);
}
}
}
else
{
//HttpCookie hc2 = new HttpCookie("openid");
//hc2.Value = openid;
//HttpContext.Current.Response.Cookies.Add(hc2);
//HttpContext.Current.Response.Redirect("~/reg.aspx");
//新增注册用户
zcUser zc = re.getZCUser(openid);
if (zc != null)
{
//有在黑店绑定过资料,跳到完善界面2
HttpContext.Current.Response.Redirect("user_info2.aspx?uid=" + openid, true);
}
else
{
HttpContext.Current.Response.Redirect("user_info.aspx?uid=" + openid + "&pic=" + wx.headimgurl + "&name=" + HttpContext.Current.Server.UrlEncode(wx.nickname), true);
}
//SqlParameter[] sp2 = { new SqlParameter("@member_name", wx.nickname), new SqlParameter("@member_mobile", ""), new SqlParameter("@member_email", ""), new SqlParameter("@member_pwd", ""), new SqlParameter("@member_number", ""), new SqlParameter("@member_openid", openid) };
//dt = Utility.SQLHelper.ExecuteTable("insert into member(member_name,member_pwd,member_mobile,member_email,member_number,member_openid) values(@member_name,@member_pwd,@member_mobile,@member_email,@member_number,@member_openid);SELECT @@IDENTITY", sp2);
//member_id = dt.Rows[0][0].ToString();
//Utility.SQLHelper.ExecuteNonQuery("insert into member_info(member_id,member_info_exp,member_info_level,member_pic) values(" + member_id + ",30,1,'" + wx.headimgurl + "')");
//Utility.SQLHelper.ExecuteNonQuery("insert into member_money(member_id,money,member_money_title) values(" + member_id + ",100,'新加入')");
//HttpCookie user = new HttpCookie("mLoginCookies");
//user.Values.Add("member_id", member_id);
//HttpContext.Current.Response.AppendCookie(user);
}
//if (HttpContext.Current.Request.Cookies["url"] != null)
//{
// HttpCookie hc = new HttpCookie("url");
// if (hc.Value != null)
// gourl = hc.Value.Replace("!", "&");
//}
//if (HttpContext.Current.Request["gourl"] != null)
// HttpContext.Current.Response.Redirect(gourl, true);
gourl = gourl.Replace("!", "&");
HttpContext.Current.Response.Redirect(gourl, true);
//string result = re.Get_Http("http://mp.qifanhui.cn/Api/Map/Fans/openid/" + openid + ".html", 3000);
//weixin_info wx = LitJson.JsonMapper.ToObject<weixin_info>(result);
//HttpCookie hc = new HttpCookie();
//hc.Name = "mLoginCookies";
//hc.Values.Add("openid", openid);
//hc.Values.Add("nickname", wx.nickname);
//hc.Values.Add("headimgurl", wx.headimgurl);
}
}
else
{
HttpContext.Current.Response.Redirect(gourl); //已经微信登录
}
}
private void BindChart()
{
// line chart is very slow to render when there are lots of data points
// bar chart is much faster
zgSales.Visible = false;
lcSales.Visible = true;
if (salesByMonthData == null)
{
salesByMonthData = CommerceReport.GetSalesByYearMonthByModule(moduleGuid);
}
StringBuilder categories = new StringBuilder();
string comma = string.Empty;
List <decimal> revenue = new List <decimal>();
// original data is sorted descending on Y, M resorting here
DataRow[] result = salesByMonthData.Select(string.Empty, "Y ASC, M ASC");
int spaceInterval = 0;
int totalItems = result.Length;
int itemsAdded = 0;
if (totalItems > 12)
{
spaceInterval = 4;
}
int nextItemToShow = 0;
foreach (DataRow row in result)
{
categories.Append(comma);
if (itemsAdded == nextItemToShow)
{
categories.Append(row["Y"].ToString());
categories.Append("-");
categories.Append(row["M"].ToString());
nextItemToShow = itemsAdded + spaceInterval;
}
comma = ",";
revenue.Add(Convert.ToDecimal(row["Sales"]));
itemsAdded += 1;
}
lcSales.ChartTitle = Resource.SalesByMonthChartLabel;
lcSales.CategoriesAxis = categories.ToString();
LineChartSeries series = new LineChartSeries();
series.Name = Resource.SalesByMonthChartSalesLabel;
series.Data = revenue.ToArray();
lcSales.Series.Add(series);
//bcSales.CategoriesAxis
//bcSales.Series.
}
private void BindDishCard(DataTable dataTable = null)
{
if (dataTable == null)
{
dataTable = new DishMenuBusiness().GetDishMenus();
}
if (this.dishPriceDT != null)
{
this.dishPriceDT.Clear();
}
this.dishPriceDT = dataTable;
if (this.currentDishPage < 1)
{
this.currentDishPage = 1;
}
if (this.dishPriceDT == null)
{
return;
}
if (this.dishPriceDT.DefaultView.Count > 0)
{
this.maxDishPageCount = Convert.ToInt32(this.dishPriceDT.DefaultView.Count / 64) + 1;
}
else
{
this.maxDishPageCount = 1;
}
if (this.currentDishPage > this.maxDishPageCount)
{
this.currentDishPage = this.maxDishPageCount;
}
if (this.dishPriceDT.DefaultView.Count > 0)
{
for (int i = 0; i < 64; i++)
{
this.dishCards[i].init();
if (((this.currentDishPage - 1) * 64) + i + 1 <= this.dishPriceDT.DefaultView.Count)
{
this.dishCards[i].DishId = Convert.ToInt32(this.dishPriceDT.DefaultView[((this.currentDishPage - 1) * 64) + i]["Id"]);
this.dishCards[i].MOID = Convert.ToInt32(this.dishPriceDT.DefaultView[((this.currentDishPage - 1) * 64) + i]["UnitId"]);
this.dishCards[i].Code = "[" + this.dishPriceDT.DefaultView[((this.currentDishPage - 1) * 64) + i]["Code"].ToString() + "]";
this.dishCards[i].DishName = this.dishCards[i].Code + "\r\n" + this.dishPriceDT.DefaultView[((this.currentDishPage - 1) * 64) + i]["Name"].ToString();
this.dishCards[i].MoName = this.dishPriceDT.DefaultView[(this.currentDishPage - 1) * 64 + i]["UnitName"].ToString();
this.dishCards[i].Price = this.dishPriceDT.DefaultView[((this.currentDishPage - 1) * 64) + i]["Price"].ToString() + "/" + this.dishCards[i].MoName;
this.dishCards[i].isgq = Convert.ToInt32(this.dishPriceDT.DefaultView[((this.currentDishPage - 1) * 64) + i]["Isgq"]); // 是否沽清
//this.dishCards[i].issetmeal = Convert.ToInt32(this.dishPriceDT.DefaultView[((this.currentDishPage - 1) * 64) + i]["ISSETMEAL"]);
this.dishCards[i].Class = Convert.ToString(this.dishPriceDT.DefaultView[((this.currentDishPage - 1) * 64) + i]["Category_Id"]);
}
}
}
else
{
for (int i = 0; i < 64; i++)
{
this.dishCards[i].init();
}
}
}
private void cbxSheet_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
System.Data.DataTable dt = tableCollection[cbxSheet.SelectedItem.ToString()];
dataGridViewData.DataSource = dt;
mList_Goc = (from DataRow dr in dt.Rows
select new LinhKien()
{
Alias = dr["Đường dẫn / Alias"].ToString(),
TenSanPham = dr["Tên sản phẩm"].ToString(),
NoiDung = dr["Nội dung"].ToString(),
NhaCungCap = dr["Nhà cung cấp"].ToString(),
Loai = dr["Loại"].ToString(),
Tag = dr["Tags"].ToString(),
HienThi = dr["Hiển thị"].ToString(),
ThuocTinh = dr["Thuộc tính 1(Option1 Name)"].ToString(),
GiaTriThuocTinh = dr["Giá trị thuộc tính 1(Option1 Value)"].ToString(),
ThuocTinh2 = dr["Thuộc tính 2(Option2 Name)"].ToString(),
GiaTriThuocTinh2 = dr["Giá trị thuộc tính 2(Option2 Value)"].ToString(),
ThuocTinh3 = dr["Thuộc tính 3(Option3 Name)"].ToString(),
GiaTriThuocTinh3 = dr["Giá trị thuộc tính 1(Option3 Value)"].ToString(),
SKU = dr["Mã (SKU)"].ToString(),
QuanLyKho = dr["Quản lý kho"].ToString(),
SoLuong = dr["Số lượng"].ToString(),
ChoPhepBan = dr["Cho phép tiếp tục mua khi hết hàng(continue/deny)"].ToString(),
Variant = dr["Variant Fulfillment Service"].ToString(),
Gia = dr["Giá"].ToString(),
GiaSoSanh = dr["Giá so sánh"].ToString(),
YeuCauVanChuyen = dr["Yêu cầu vận chuyển"].ToString(),
VAT = dr["VAT"].ToString(),
MaVach = dr["Mã vạch(Barcode)"].ToString(),
AnhDaiDien = dr["Ảnh đại diện"].ToString(),
ChuThich = dr["Chú thích ảnh"].ToString(),
TheTieuDe = dr["Thẻ tiêu đề(SEO Title)"].ToString(),
TheMoTa = dr["Thẻ mô tả(SEO Description)"].ToString(),
CanNang = dr["Cân nặng"].ToString(),
DonViCan = dr["Đơn vị cân nặng"].ToString(),
AnhPhienBan = dr["Ảnh phiên bản"].ToString(),
MoTaNgan = dr["Mô tả ngắn"].ToString(),
ID = dr["Id sản phẩm"].ToString(),
IDTuyChon = dr["Id tùy chọn"].ToString()
}).ToList();
for (int x = 0; x < mList_Goc.Count; x++)
{
if (mList.FirstOrDefault(v => v.ID == mList_Goc[x].ID) == null)
{
LinhKien _linhkien = new LinhKien();
_linhkien = mList_Goc[x];
if (mList_Goc[x].AnhDaiDien == "")
{
}
else
{
_linhkien.mListAnh.Add(mList_Goc[x].AnhDaiDien);
}
mList.Add(_linhkien);
}
else
{
int stt = mList.FindIndex(v => v.ID == mList_Goc[x].ID);
mList[stt].mListAnh.Add(mList_Goc[x].AnhDaiDien);
}
}
}
catch (Exception Ex)
{
MessageBox.Show("Bảng Excel đang được sử dụng bởi một ứng dụng khác", "Thông Báo");
MessageBox.Show(Ex.ToString());
}
}
public void SelectByPrimaryKey(HPS.BLL.OutCameraPicturesBLL.BLLOutCameraPictures_TKeys businessObjectKey, ref System.Data.DataTable dataTable)
{
bool ControlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(this.Command);
this.Command.CommandText = "[sp_OutCameraPictures_T_SelectByPrimaryKey]";
this.Command.CommandType = CommandType.StoredProcedure;
try
{
this.Command.Parameters.Clear();
SqlParameter OutCameraPicturesID_bint = new SqlParameter();
OutCameraPicturesID_bint.ParameterName = "@OutCameraPicturesID_bint";
OutCameraPicturesID_bint.SqlDbType = SqlDbType.BigInt;
OutCameraPicturesID_bint.Direction = ParameterDirection.Input;
OutCameraPicturesID_bint.IsNullable = false;
OutCameraPicturesID_bint.Value = businessObjectKey.OutCameraPicturesID_bint;
this.Command.Parameters.Add(OutCameraPicturesID_bint);
if (ControlConnection)
{
this.BeginTransaction();
}
sqlDataAdapter.Fill(dataTable);
if (ControlConnection)
{
this.Commit();
}
}
catch (System.Exception ex)
{
if (ControlConnection)
{
this.RollBack();
}
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
#region 初始化下拉框
System.Data.DataTable dtUserGroup = Common.GetObjectList("UserGroup");
ViewState["UserGroup"] = dtUserGroup;
ddlUserGroup.DataSource = dtUserGroup;
ddlUserGroup.DataValueField = "UserGroupID";
ddlUserGroup.DataTextField = "UserGroupName";
ddlUserGroup.DataBind();
ddlUserGroup.Items.Insert(0, new ListItem("请选择", ""));
ddlWebRole.DataSource = Common.GetObjectList("Role");
ddlWebRole.DataValueField = "RoleID";
ddlWebRole.DataTextField = "RoleName";
ddlWebRole.DataBind();
ddlWebRole.Items.Insert(0, new ListItem("请选择", ""));
#endregion
if (Request.QueryString["Type"] != null &&
Request.QueryString["Type"].ToString() == "0")
{
//新增
lblMsg.Text = "新增用户";
lblRemark.Visible = true;
}
else
{
lblMsg.Text = "更新用户";
txtJobNo.ReadOnly = true;
if (Request.QueryString["UserId"] != null)
{
int userId = int.Parse(Request.QueryString["UserId"].ToString());
System.Data.DataTable dt = Common.GetObjectList("User", userId);
if (dt != null && dt.Rows.Count > 0)
{
this.txtJobNo.Text = dt.Rows[0]["JobNo"] as string;
this.txtRealName.Text = dt.Rows[0]["RealName"] as string;
this.txtJobDuty.Text = dt.Rows[0]["JobDuty"] as string;
if (int.Parse(dt.Rows[0]["UserGroupID"].ToString()) > 0)
{
this.ddlUserGroup.SelectedValue = dt.Rows[0]["UserGroupID"].ToString();
}
if (int.Parse(dt.Rows[0]["RoleID"].ToString()) > 0)
{
this.ddlWebRole.SelectedValue = dt.Rows[0]["RoleID"].ToString();
this.panWebRole.Visible = true;
}
ViewState["UserID"] = dt.Rows[0]["UserID"];
ViewState["Password"] = dt.Rows[0]["Password"];
}
}
}
}
}
public AttLogs.ServiceReference1.ImportInfo ImportBioPunchs(string CompanyIdentification, string UserIdentification, System.Data.DataTable UploadTable)
{
return(base.Channel.ImportBioPunchs(CompanyIdentification, UserIdentification, UploadTable));
}
private void buttonX6_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
int hluc = 0;
if (cbHieuluc_S.Text == "Hoạt động")
{
hluc = 1;
}
else
{
hluc = 0;
}
String sCommand = "SELECT chinhsach.*,nhomkhachhang.tennhom from Chinhsach,nhomkhachhang where chinhsach.manhom=nhomkhachhang.manhom and chinhsach.macn='" + CRM.frmMain.cn + "' and chinhsach.hieuluc =" + hluc + "";
if (frmMain.conn.State == ConnectionState.Open)
{
frmMain.conn.Close();
}
frmMain.conn.Open();
new SqlDataAdapter(sCommand, frmMain.conn).Fill(dt);
frmMain.conn.Close();
System.Data.DataTable dskh = new System.Data.DataTable();
DataColumn col = null;
col = new DataColumn("STT", typeof(int));
dskh.Columns.Add(col);
col = new DataColumn("Mã chính sách", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Tên", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Nội dung", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Ghi chú", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Tên nhóm KH", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Ngày bắt đầu", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Ngày kết thúc", typeof(string));
dskh.Columns.Add(col);
col = new DataColumn("Hiệu lực", typeof(string));
dskh.Columns.Add(col);
int iRows = dt.Rows.Count;
for (int i = 0; i < iRows; i++)
{
try
{
DataRow row = dskh.NewRow();
row[0] = i + 1;
row[1] = dt.Rows[i]["macs"].ToString();
row[2] = dt.Rows[i]["ten"].ToString();
row[3] = dt.Rows[i]["noidung"].ToString();
row[4] = dt.Rows[i]["ghichu"].ToString();
row[5] = dt.Rows[i]["tennhom"].ToString();
row[6] = dt.Rows[i]["batdau"].ToString().Substring(0, 10);
row[7] = dt.Rows[i]["ketthuc"].ToString().Substring(0, 10);
if (dt.Rows[i]["Hieuluc"].ToString() == "True")
{
row[8] = "Hoạt động";
}
else
{
row[8] = "Ngưng hoạt động";
}
dskh.Rows.Add(row);
}
catch { }
}
dataGridViewX2.DataSource = dskh;
dataGridViewX2.Columns[0].ReadOnly = true;
dataGridViewX2.Columns[2].ReadOnly = true;
dataGridViewX2.Columns[3].ReadOnly = true;
dataGridViewX2.Columns[4].ReadOnly = true;
dataGridViewX2.Columns[5].ReadOnly = true;
dataGridViewX2.Columns[6].ReadOnly = true;
dataGridViewX2.Columns[7].ReadOnly = false;
dataGridViewX2.Columns[8].ReadOnly = false;
dataGridViewX2.Columns[0].Width = 30;
dataGridViewX2.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewX2.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewX2.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewX2.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewX2.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridViewX2.Columns[1].Visible = false;
}
public static void DataSetToExcel(System.Data.DataTable dataTable, string filename)//传入查询的表以及存储的路径
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
// if equal null means EXCEL is not installed.
MessageBox.Show("Excel is not properly installed!");
return;
}
Workbook workBook;
if (File.Exists(filename))
{
workBook = excel.Workbooks.Open(filename, 0, false, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
}
else
{
workBook = excel.Workbooks.Add(true);
}
//new a worksheet
Worksheet workSheet = workBook.ActiveSheet as Worksheet;
//write data
workSheet = (Worksheet)workBook.Worksheets.get_Item(1);//获得第i个sheet，准备写入
//set visible the Excel will run in background
excel.Visible = false;
//set false the alerts will not display
excel.DisplayAlerts = false;
//取得标题
int rowIndex = 1;
int colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//取得表格中的数据
foreach (DataRow row in dataTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName];
////设置表格内容居中对齐
//workSheet.Range[excel.Cells[rowIndex, colIndex],
// excel.Cells[rowIndex, colIndex]].HorizontalAlignment =XlVAlign.xlVAlignCenter;
}
//if (rowIndex%500 == 0)
//{
// MessageBox.Show(rowIndex.ToString());
//}
}
MessageBox.Show("导出完成");
dataTable = null;
workBook.SaveAs(filename);
workBook.Close(false, Missing.Value, Missing.Value);
//quit and clean up objects
excel.Quit();
workSheet = null;
workBook = null;
excel = null;
GC.Collect();
}
/// <summary>
/// Sort DataTable by Column asc or dsc
/// </summary>
/// <param name="_dtb">DataTable Source </param>
/// <param name="_ColName">Sort by Column</param>
/// <param name="_Direction"> </param>
/// <returns></returns>
public static System.Data.DataTable SortDataTable(System.Data.DataTable _dtb, string _ColName, string _Direction)
{
_dtb.DefaultView.Sort = _ColName + " " + _Direction;
_dtb = _dtb.DefaultView.ToTable();
return(_dtb);
}
public static string CreateWordDocument(DataTable dataTable)
{
var application = new ApplicationClass();
var template = Type.Missing;
var newTemplate = Type.Missing;
var documentType = Type.Missing;
var visible = Type.Missing;
var document = application.Documents.Add(ref template, ref newTemplate, ref documentType, ref visible);
application.Selection.Font.Name = "Tahoma";
application.Selection.Font.Size = 8;
var range = application.Selection.Range;
var defaultTableBehaviour = Type.Missing;
object autoFitBehaviour = WdAutoFitBehavior.wdAutoFitContent;
var numOfRows = dataTable.Rows.Count + 1;
var numOfColumns = Math.Min(dataTable.Columns.Count, 63);
string text = null;
const string separator = "\t";
for (var i = 0; i < numOfColumns - 1; i++)
{
text += dataTable.Columns[i].ColumnName + separator;
}
text += dataTable.Columns[numOfColumns - 1].ColumnName + Environment.NewLine;
foreach (DataRow dataRow in dataTable.Rows)
{
for (var i = 0; i < numOfColumns - 1; i++)
{
text += QueryForm.DbValue(dataRow[i]) + separator;
}
text += QueryForm.DbValue(dataRow[numOfColumns - 1]) + Environment.NewLine;
}
application.Selection.InsertAfter(text);
var missing = Type.Missing;
object format = WdTableFormat.wdTableFormatList4;
var table = application.Selection.Range.ConvertToTable(ref missing, ref missing, ref missing, ref missing,
ref format, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);
table.AutoFitBehavior(WdAutoFitBehavior.wdAutoFitContent);
table.Columns.AutoFit();
foreach (Microsoft.Office.Interop.Word.Column column in table.Columns)
{
column.Select();
column.AutoFit();
}
var fileName = Path.GetTempFileName();
object fileNameObj = fileName;
object fileFormat = WdSaveFormat.wdFormatRTF;
document.SaveAs(ref fileNameObj, ref fileFormat, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing);
document.Close(ref missing, ref missing, ref missing);
application.Quit(ref missing, ref missing, ref missing);
return(fileName);
}
public object GYSHXList(int pagnum, int pagesize, string stime, string etime, string username, string ddbm, string ishx)
{
using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection())
{
try
{
int cp = pagnum;
int ac = 0;
string where = "";
if (!string.IsNullOrEmpty(username))
{
where += " and " + dbc.C_Like("f.username", username.Trim(), LikeStyle.LeftAndRightLike);
}
if (stime != null && stime != "")
{
where += " and c.shippingnoteadddatetime>=" + dbc.ToSqlValue(stime);
}
if (etime != null && etime != "")
{
where += " and c.shippingnoteadddatetime<=" + dbc.ToSqlValue(etime);
}
if (ddbm != null && ddbm != "")
{
where += " and c.shippingnotenumber like '%" + ddbm + "%'";
}
string str = @"
select * from (
SELECT g.id as costid,g.money,h.username AS um,g.verifymoney as hxmoney,e.descriptionofgoods,e.goodsfromroute,e.goodstoroute,e.goodsreceiptplace
,c.offerid,(SELECT SUM(verifymoney) FROM tb_b_shippingnoteinfo_verify WHERE costid=g.id) AS verifymoney,e.actualmoney,e.actualwaymoney,f.username,f.userid,c.shippingnoteid,c.shippingnoteadddatetime,c.shippingnotenumber,c.statisticstype,d.totalamount,d.totalvaloremtax,d.rate,d.billingtime,d.invoicecode,d.invoicenumber FROM
tb_b_shippingnoteinfo c LEFT JOIN
(SELECT a.shippingnoteid,b.totalvaloremtax,b.rate,b.billingtime,b.invoicecode,b.invoicenumber,b.totalamount
FROM tb_b_invoicedetail a LEFT JOIN tb_b_invoice b ON a.billingid=b.billingid
) d ON c.shippingnoteid=d.shippingnoteid
LEFT JOIN tb_b_sourcegoodsinfo_offer e ON c.offerid=e.offerid
INNER JOIN tb_b_user h ON e.shipperid=h.userid
left join tb_b_shippingnoteinfo_cost g on c.shippingnoteid=g.shippingnoteid and g.status = 0 and g.usertype = 2
INNER JOIN tb_b_user f ON g.userid=f.userid AND f.usertype=2
WHERE c.shippingnotestatuscode >= 30 and c.isdeleteflag = 0
";
str += where + " ORDER BY c.consignmentdatetime) AS tab ";
if (ishx != null && ishx != "")
{
if (ishx == "0")
{
str += " where (hxmoney=0 or hxmoney is null)";
}
else if (ishx == "1")
{
str += " where hxmoney<money ";
}
else if (ishx == "2")
{
str += " where hxmoney=money ";
}
}
//开始取分页数据
System.Data.DataTable dtPage = new System.Data.DataTable();
dtPage = dbc.GetPagedDataTable(str + " ", pagesize, ref cp, out ac);
return(new { dt = dtPage, cp = cp, ac = ac });
}
catch (Exception ex)
{
throw ex;
}
}
}
private void btnOpen_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.DefaultExt = ".xlsx";
openfile.Filter = "(.xlsx)|*.xlsx";
var browsefile = openfile.ShowDialog();
if (browsefile == true)
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
//Static File From Base Path...........
//Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + "TestExcel.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Dynamic File Using Uploader...........
Workbook excelBook = excelApp.Workbooks.Open(openfile.FileName.ToString(), 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Worksheet excelSheet = (Worksheet)excelBook.Sheets[1];
Range excelRange = excelSheet.UsedRange;
int rowCnt = excelRange.Rows.Count;
int colCnt = excelRange.Columns.Count;
string strCellData = "";
double douCellData;
DataTable table;
table = new DataTable();
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
string strColumn = "";
strColumn = (string)(excelRange.Cells[1, colCnt] as Range).Value2;
table.Columns.Add(strColumn, typeof(string));
}
for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
{
string strData = "";
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
if ((excelRange.Cells[rowCnt, colCnt] as Range).Value2 is string)
{
strCellData = (excelRange.Cells[rowCnt, colCnt] as Range).Value2;
strData += strCellData + "|";
}
else if ((excelRange.Cells[rowCnt, colCnt] as Range).Value2 is double)
{
douCellData = (excelRange.Cells[rowCnt, colCnt] as Range).Value2;
strData += douCellData.ToString() + "|";
}
else
{
strData += "" + "|";
}
}
strData = strData.Remove(strData.Length - 1, 1);
table.Rows.Add(strData.Split('|'));
}
dtGrid.ItemsSource = table.DefaultView;
excelBook.Close(true, null, null);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
Console.ReadLine();
}
}
public byte[] ExportSourceGoods(string stime, string etime, string username, string ddbm, string ishx)
{
using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection())
{
try
{
string where = "";
if (!string.IsNullOrEmpty(username))
{
where += " and " + dbc.C_Like("f.username", username.Trim(), LikeStyle.LeftAndRightLike);
}
if (stime != null && stime != "")
{
where += " and c.shippingnoteadddatetime>=" + dbc.ToSqlValue(stime);
}
if (etime != null && etime != "")
{
where += " and c.shippingnoteadddatetime<=" + dbc.ToSqlValue(etime);
}
if (ddbm != null && ddbm != "")
{
where += " and c.shippingnotenumber like '%" + ddbm + "%'";
}
string str = @"
select *,(money - hxmoney) symoney from (
SELECT g.id as costid,g.money,h.username AS um,case when g.verifymoney is not null then g.verifymoney else 0 end hxmoney,e.descriptionofgoods,e.goodsfromroute,e.goodstoroute,e.goodsreceiptplace
,c.offerid,(SELECT SUM(verifymoney) FROM tb_b_shippingnoteinfo_verify WHERE costid=g.id) AS verifymoney,e.actualmoney,e.actualwaymoney,f.username,f.userid,c.shippingnoteid,c.shippingnoteadddatetime,c.shippingnotenumber,c.statisticstype,d.totalamount,d.totalvaloremtax,d.rate,d.billingtime,d.invoicecode,d.invoicenumber FROM
tb_b_shippingnoteinfo c LEFT JOIN
(SELECT a.shippingnoteid,b.totalvaloremtax,b.rate,b.billingtime,b.invoicecode,b.invoicenumber,b.totalamount
FROM tb_b_invoicedetail a LEFT JOIN tb_b_invoice b ON a.billingid=b.billingid
) d ON c.shippingnoteid=d.shippingnoteid
LEFT JOIN tb_b_sourcegoodsinfo_offer e ON c.offerid=e.offerid
INNER JOIN tb_b_user h ON e.shipperid=h.userid
left join tb_b_shippingnoteinfo_cost g on c.shippingnoteid=g.shippingnoteid and g.status = 0 and g.usertype = 2
INNER JOIN tb_b_user f ON g.userid=f.userid AND f.usertype=2
WHERE c.shippingnotestatuscode >= 30 and c.isdeleteflag = 0
";
str += where + " ORDER BY c.consignmentdatetime) AS tab ";
if (ishx != null && ishx != "")
{
if (ishx == "0")
{
str += " where (hxmoney=0 or hxmoney is null)";
}
else if (ishx == "1")
{
str += " where hxmoney<money ";
}
else if (ishx == "2")
{
str += " where hxmoney=money ";
}
}
//开始取分页数据
System.Data.DataTable dt = new System.Data.DataTable();
dt = dbc.ExecuteDataTable(str);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表
Aspose.Cells.Cells cells = sheet.Cells; //单元格
#region 样式
//样式1
Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //新增样式
style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style1.Font.Name = "宋体"; //文字字体
style1.Font.Size = 12; //文字大小
style1.IsTextWrapped = true; //单元格内容自动换行
style1.Font.IsBold = true; //粗体
style1.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style1.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//样式2
Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式
style2.HorizontalAlignment = TextAlignmentType.Left; //文字居左
style2.Font.Name = "宋体"; //文字字体
style2.Font.Size = 12; //文字大小
style2.IsTextWrapped = true; //单元格内容自动换行
style2.Font.IsBold = false; //粗体
style2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//样式3
Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式
style3.HorizontalAlignment = TextAlignmentType.Center; //文字居左
style3.Font.Name = "宋体"; //文字字体
style3.Font.Size = 20; //文字大小
style3.IsTextWrapped = true; //单元格内容自动换行
style3.Font.IsBold = true; //粗体
//样式4
Aspose.Cells.Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式
style4.HorizontalAlignment = TextAlignmentType.Left; //文字居左
style4.Font.Name = "宋体"; //文字字体
style4.Font.Size = 10; //文字大小
style4.Font.Color = Color.Red; //文字大小
style4.IsTextWrapped = true; //单元格内容自动换行
style4.Font.IsBold = false; //粗体
#endregion
String[] ColumnName = { "订单时间", "厂家", "单号", "起始地", "目的地", "收货地址", "货物", "供应商", "供应商总付款", "已核销金额", "剩余可核销金额" };
String[] ColumnV = { "shippingnoteadddatetime", "um", "shippingnotenumber", "goodsfromroute", "goodstoroute", "goodsreceiptplace", "descriptionofgoods", "username", "money", "hxmoney", "symoney" };
for (int i = 0; i < ColumnName.Length; i++)
{
cells.SetColumnWidth(i, 30);
cells[0, i].PutValue(ColumnName[i]);
cells[0, i].SetStyle(style1);
}
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int a = 0; a < ColumnV.Length; a++)
{
var ColumnVs = dt.Rows[i][ColumnV[a]].ToString();
cells[i + 1, a].PutValue(ColumnVs);
cells[i + 1, a].SetStyle(style2);
}
}
}
System.IO.MemoryStream ms = workbook.SaveToStream();
byte[] bt = ms.ToArray();
return(bt);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.components = new System.ComponentModel.Container();
System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1));
this.gridControl1 = new DevExpress.XtraGrid.GridControl();
this.bindingSource1 = new System.Windows.Forms.BindingSource(this.components);
this.dataSet1 = new System.Data.DataSet();
this.dataTable1 = new System.Data.DataTable();
this.dataColumn1 = new System.Data.DataColumn();
this.dataColumn2 = new System.Data.DataColumn();
this.dataColumn3 = new System.Data.DataColumn();
this.gridView1 = new DevExpress.XtraGrid.Views.Grid.GridView();
this.colParameter = new DevExpress.XtraGrid.Columns.GridColumn();
this.colUnit = new DevExpress.XtraGrid.Columns.GridColumn();
this.repositoryItemComboBox1 = new DevExpress.XtraEditors.Repository.RepositoryItemComboBox();
this.colValue = new DevExpress.XtraGrid.Columns.GridColumn();
((System.ComponentModel.ISupportInitialize)(this.gridControl1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.bindingSource1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.dataTable1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.gridView1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.repositoryItemComboBox1)).BeginInit();
this.SuspendLayout();
//
// gridControl1
//
this.gridControl1.DataSource = this.bindingSource1;
resources.ApplyResources(this.gridControl1, "gridControl1");
this.gridControl1.MainView = this.gridView1;
this.gridControl1.Name = "gridControl1";
this.gridControl1.RepositoryItems.AddRange(new DevExpress.XtraEditors.Repository.RepositoryItem[] {
this.repositoryItemComboBox1
});
this.gridControl1.ViewCollection.AddRange(new DevExpress.XtraGrid.Views.Base.BaseView[] {
this.gridView1
});
//
// bindingSource1
//
this.bindingSource1.DataMember = "Table1";
this.bindingSource1.DataSource = this.dataSet1;
//
// dataSet1
//
this.dataSet1.DataSetName = "NewDataSet";
this.dataSet1.Locale = new System.Globalization.CultureInfo("en-US");
this.dataSet1.Tables.AddRange(new System.Data.DataTable[] {
this.dataTable1
});
//
// dataTable1
//
this.dataTable1.Columns.AddRange(new System.Data.DataColumn[] {
this.dataColumn1,
this.dataColumn2,
this.dataColumn3
});
this.dataTable1.TableName = "Table1";
//
// dataColumn1
//
this.dataColumn1.ColumnName = "Parameter";
//
// dataColumn2
//
this.dataColumn2.ColumnName = "Unit";
//
// dataColumn3
//
this.dataColumn3.ColumnName = "Value";
this.dataColumn3.DataType = typeof(decimal);
//
// gridView1
//
this.gridView1.Columns.AddRange(new DevExpress.XtraGrid.Columns.GridColumn[] {
this.colParameter,
this.colUnit,
this.colValue
});
this.gridView1.GridControl = this.gridControl1;
resources.ApplyResources(this.gridView1, "gridView1");
this.gridView1.Name = "gridView1";
this.gridView1.ShownEditor += new System.EventHandler(this.gridView1_ShownEditor);
//
// colParameter
//
resources.ApplyResources(this.colParameter, "colParameter");
this.colParameter.FieldName = "Parameter";
this.colParameter.Name = "colParameter";
//
// colUnit
//
resources.ApplyResources(this.colUnit, "colUnit");
this.colUnit.ColumnEdit = this.repositoryItemComboBox1;
this.colUnit.FieldName = "Unit";
this.colUnit.Name = "colUnit";
//
// repositoryItemComboBox1
//
resources.ApplyResources(this.repositoryItemComboBox1, "repositoryItemComboBox1");
this.repositoryItemComboBox1.Buttons.AddRange(new DevExpress.XtraEditors.Controls.EditorButton[] {
new DevExpress.XtraEditors.Controls.EditorButton(((DevExpress.XtraEditors.Controls.ButtonPredefines)(resources.GetObject("repositoryItemComboBox1.Buttons"))))
});
this.repositoryItemComboBox1.Name = "repositoryItemComboBox1";
//
// colValue
//
resources.ApplyResources(this.colValue, "colValue");
this.colValue.FieldName = "Value";
this.colValue.Name = "colValue";
//
// Form1
//
resources.ApplyResources(this, "$this");
this.Controls.Add(this.gridControl1);
this.Name = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.gridControl1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.bindingSource1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataTable1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.gridView1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.repositoryItemComboBox1)).EndInit();
this.ResumeLayout(false);
}
// http://portal.mediclinic.com.au:803/Reminder/RunUnsentReferrerEPCLetters.aspx?pwd=mah_sms_reminder
// http://portal.mediclinic.com.au:803/Reminder/RunUnsentReferrerEPCLetters.aspx?pwd=mah_sms_reminder&inc_sending=false
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Utilities.SetNoCache(Response);
}
try
{
string pwd = Request.Form["pwd"];
if (pwd != null) // sent by http post
{
if (pwd == null || pwd != System.Configuration.ConfigurationManager.AppSettings["SMSRunRemindersPwd"])
{
throw new CustomMessageException("Incorrect password");
}
string exceptionOutput = string.Empty;
if (Session != null && Session["DB"] != null)
{
Run(false, true);
}
else
{
if (Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["UseConfigDB"]))
{
Session["DB"] = System.Configuration.ConfigurationManager.AppSettings["Database"];
Session["SystemVariables"] = SystemVariableDB.GetAll();
Run(false, true);
Session.Remove("DB");
Session.Remove("SystemVariables");
}
else // Get all DB's and run it on all of them
{
System.Data.DataTable tbl = DBBase.ExecuteQuery("EXEC sp_databases;", "master").Tables[0];
for (int i = 0; i < tbl.Rows.Count; i++)
{
string databaseName = tbl.Rows[i][0].ToString();
if (!Regex.IsMatch(databaseName, @"Mediclinic_\d{4}"))
{
continue;
}
try
{
Session["DB"] = databaseName;
Session["SystemVariables"] = SystemVariableDB.GetAll();
Run(false, true);
}
catch (Exception ex)
{
exceptionOutput += Environment.NewLine + DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + " " + "DB: " + databaseName;
exceptionOutput += Environment.NewLine + ex.ToString();
}
finally
{
Session.Remove("DB");
Session.Remove("SystemVariables");
}
}
}
}
if (exceptionOutput.Length > 0)
{
Response.Write("Run Completed But With Errors!");
Response.Write(Environment.NewLine + exceptionOutput);
}
else
{
Response.Write("Run Completed!");
}
}
else // send in url by http get
{
pwd = Request.QueryString["pwd"];
if (pwd == null || pwd != System.Configuration.ConfigurationManager.AppSettings["SMSRunRemindersPwd"])
{
throw new CustomMessageException("Incorrect password");
}
string inc_sending = Request.QueryString["inc_sending"];
if (inc_sending != null && inc_sending != "true" && inc_sending != "false")
{
throw new CustomMessageException("Incorrect inc_sending value");
}
bool incSending = inc_sending != null && inc_sending == "true";
if (Session != null && Session["DB"] != null)
{
Run(true, incSending);
}
else
{
if (!Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["UseConfigDB"]))
{
throw new CustomMessageException("Can not run for logged out user where UseConfigDB = false");
}
else
{
Session["DB"] = System.Configuration.ConfigurationManager.AppSettings["Database"];
Session["SystemVariables"] = SystemVariableDB.GetAll();
Run(true, incSending);
Session.Remove("DB");
Session.Remove("SystemVariables");
}
}
}
}
catch (CustomMessageException ex)
{
Response.Write(ex.Message);
}
catch (Exception ex)
{
Response.Write("Exception: " + (ex.ToString()));
}
}