private void DoallThetracking(string trackkey, string value)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = "Server=devrystudentsp10.db.6077598.hostedresource.com;User Id=DeVryStudentSP10;Password=OidLZqBv4;";
conn.Open();
// Console.WriteLine(conn.State);
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
comm.Connection = conn;
string SQL = "insert into huber_tracker12 (usertrackerid, trackkey, value,trackwhen) values (NEWID(), @trackkey, @trackvalue, @trackwhen)";
comm.CommandText = SQL;
comm.Parameters.AddWithValue("@trackkey", trackkey);
comm.Parameters.AddWithValue("@trackvalue", value);
comm.Parameters.AddWithValue("@trackwhen", DateTime.Now);
comm.ExecuteNonQuery();
SQL = "delete from huber_tracker12 where usertrackerid not in (select top 300 usertrackerid from huber_tracker12 order by trackwhen desc)";
comm.CommandText = SQL;
comm.ExecuteNonQuery();
conn.Close();
}
public static bool ChangeProduct( System.Data.SqlClient.SqlConnection connection,
System.Data.SqlClient.SqlTransaction tran,
object old_product_id, object new_product_id, out string error)
{
bool done = false;
error = "";
try
{
connection.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
string query = "UPDATE Purchases.ReceiptContents SET ProductID = @NewProduct\n" +
"WHERE ProductID = @OldProduct";
cmd.Parameters.AddWithValue("@NewProduct", new_product_id);
cmd.Parameters.AddWithValue("@OldProduct", old_product_id);
cmd.CommandTimeout = 0;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Connection = connection;
if (tran != null) cmd.Transaction = tran;
cmd.ExecuteNonQuery();
connection.Close();
done = true;
}
catch (System.Exception ex)
{
error = ex.Message;
}
finally
{
if (connection.State == System.Data.ConnectionState.Open) connection.Close();
}
return done;
}
public void InsertError(Exception err, int refId, bool sendMail)
{
string conn = System.Configuration.ConfigurationManager.ConnectionStrings["MainConnection"].ConnectionString;
string queryString = "INSERT INTO [MLB].[dbo].[Error] ([RefId],[ErrorMethod],[ErrorText],[ErrorDate]) VALUES (@RefId,@ErrorMethod,@ErrorText,@ErrorDate)";
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(conn))
{
// Create the Command and Parameter objects.
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@RefId", refId);
command.Parameters.AddWithValue("@ErrorMethod", err.Source);
command.Parameters.AddWithValue("@ErrorText", err.InnerException);
command.Parameters.AddWithValue("@ErrorDate", DateTime.Now);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{ }
}
if (sendMail)
{
var email = EmailMessageFactory.GetErrorEmail(err);
var result = EmailClient.SendEmail(email);
}
}
private void AutoSignOff()
{
try
{
bool autoOff = bool.Parse(ConfigurationManager.AppSettings["AutoSignOff"]);
int autoOffTime = int.Parse(ConfigurationManager.AppSettings["AutoSignOffMinutes"]);
string time = DateTime.Now.AddMinutes(-autoOffTime).ToString("yyyy-MM-dd HH:mm:ss");
if (!autoOff)
return;
try
{
var con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["Entities"].ConnectionString);
var com = new System.Data.SqlClient.SqlCommand(string.Format("DELETE FROM UserOnline WHERE [TimeStamp] < '{0}'", time), con);
con.Open();
com.ExecuteNonQuery();
try
{
con.Close();
}
catch
{
}
}
catch
{
}
}
catch
{
}
}
public static bool AddGroup(string intranetGroupName)
{
try
{
int groupID = 0;
//Получаем идентификатор группы
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("INSERT INTO groups(group_id,group_name) VALUES((SELECT MIN(group_id)-1 FROM groups WHERE id>-1000), @name)", conn))
{
try
{
conn.Open();
cmd.Parameters.AddWithValue("@name", intranetGroupName);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
return true;
}
catch (Exception ex)
{
Core.Logging.WriteEntry("Ошибка добавления группы.", ex, 1);
return false;
}
}
// Занесение новой записи в БД
public static bool Insert(System.Data.SqlClient.SqlConnection connection, System.Data.DataRow row, out string message)
{
bool done = false;
message = "";
try{
connection.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
string sQuery = "INSERT INTO " + Maker.Table + "\n" +
" (MakerID, Name, MakerCategory, Vendor, Address, Created, Updated)\n" +
"VALUES (@MakerID, @Name, @MakerCategory, @Vendor, @Address, GETDATE(), GETDATE())";
cmd.Parameters.AddWithValue("@MakerID", row["MakerID"]);
cmd.Parameters.AddWithValue("@Name", row["Name"]);
cmd.Parameters.AddWithValue("@MakerCategory", row["MakerCategory"]);
cmd.Parameters.AddWithValue("@Vendor", row["Vendor"]);
cmd.Parameters.AddWithValue("@Address", row["Address"]);
//cmd.Parameters.AddWithValue("@Created", row["Created"]);
//cmd.Parameters.AddWithValue("@Updated", row["Updated"]);
cmd.Connection = connection;
cmd.CommandTimeout = 0;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sQuery;
cmd.ExecuteNonQuery();
connection.Close();
done = true;
}catch (System.Exception ex){
message = ex.Message;
}finally{
if (connection.State == System.Data.ConnectionState.Open) connection.Close();
}
return done;
}
public static bool Delete(System.Data.SqlClient.SqlConnection connection,
System.Data.SqlClient.SqlTransaction tran,
System.Data.DataRow row, out string message)
{
bool done = false;
message = "";
try
{
connection.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
string sQuery = "DELETE FROM Producer.Products\n" +
" WHERE ProductID = @ProductID";
cmd.Parameters.AddWithValue("@ProductID", row["ProductID"]);
cmd.Connection = connection;
if (tran != null) cmd.Transaction = tran;
cmd.CommandTimeout = 0;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sQuery;
cmd.ExecuteNonQuery();
connection.Close();
done = true;
}
catch (System.Exception ex)
{
message = ex.Message;
}
finally
{
if (connection.State == System.Data.ConnectionState.Open) connection.Close();
}
return done;
}
protected void ExecutionConfirmed(object sender, EventArgs e)
{
panelMain.Visible = true;
confirmPanel.Visible = false;
System.ServiceProcess.ServiceController sc = new System.ServiceProcess.ServiceController("EOBProcessing");
try
{
sc.Stop();
System.Threading.Thread.Sleep(5000);
dbProcedures db = new dbProcedures();
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("usp_AddOneTimeRunSchedule", new db().SqlConnection);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlCmd.ExecuteNonQuery();
db.Close();
sc.Start();
lblMessage.Text = "EOB System manual execution started.";
}
catch (Exception e1)
{
lblMessage.Text = "Failed to manually start EOB System service." + e1.Message();
}
}
private void btnBackup_Click(object sender, EventArgs e)
{
if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
{
try
{
string path = String.Format("{0}\\{1}PharmaInventory{2:MMMddyyyy}.bak", folderBrowserDialog1.SelectedPath, GeneralInfo.Current.HospitalName, DateTimeHelper.ServerDateTime);
string connectionString = readApp.GetValue("dbConnection", typeof(string)).ToString();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand();
if (!(conn.State == ConnectionState.Open))
conn.Open();
string dbName = conn.Database;
com.CommandText = "BACKUP DATABASE [" + dbName + "] TO DISK = N'" + path + "' WITH NOFORMAT, NOINIT, NAME = N'PharmaInventory" + DateTimeHelper.ServerDateTime.ToString("MMMddyyyy") + "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
com.Connection = conn;
com.ExecuteNonQuery();
GeneralInfo.Current.LastBackUp = DateTimeHelper.ServerDateTime;
GeneralInfo.Current.Save();
MessageBox.Show(@"Backup completed to " + path + @"!", @"Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show(@"Backup has failed! Please Try Again.",@"Try Again",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
}
//Metodo de Iserción de Datos
public bool Insertar(Comun.Vars p)
{
bool Resultado = false;
//Realizar metodo de conexión
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
//Activa o Desactivar String de Conexion
cn.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Wayvas\\Task\\Presentacion\\App_Data\\Task.mdf;Integrated Security=True;User Instance=True";
//cn.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=True";
cn.Open();//Iniciamos la Conexion
System.Data.SqlClient.SqlCommand cm =new System.Data.SqlClient.SqlCommand();
cm.Connection=cn;
cm.CommandType=System.Data.CommandType.Text;
cm.CommandText = "Insert into Task(TaskName,StartDate,DueDate,CompDate,TaskComs) Values(@P_TaskName,@P_StartDate,@P_DueDate,@P_CompDate,@P_TaskComs)";
cm.Parameters.AddWithValue("@P_TaskName", p.TaskName);
cm.Parameters.AddWithValue("@P_StartDate", p.StartDate);
cm.Parameters.AddWithValue("@P_DueDate", p.DueDate);
cm.Parameters.AddWithValue("@P_CompDate", p.CompDate);
cm.Parameters.AddWithValue("@P_TaskComs", p.TaskComs);
int ra = cm.ExecuteNonQuery();
if (ra==1)
{ Resultado=true;}
cn.Close();
cn.Dispose();
return Resultado;
}
public static int DeleteFromSqlTable(string tableName, string columnName, string key, System.Data.SqlClient.SqlConnection connection)
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandText = "delete from " + tableName + " where " + columnName + " = " + key;
cmd.Connection = connection;
return cmd.ExecuteNonQuery();
}
private void btnSave_Click(object sender, EventArgs e)
{
string connectionstring = "Data Source=devrystudentsp10.db.6077598.hostedresource.com;Persist Security Info=True;User ID=DeVryStudentSP10;Password=OidLZqBv4";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = connectionstring;
conn.Open();
//MessageBox.Show(conn.State.ToString());
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
comm.Connection = conn;
comm.CommandText = "insert into Huber_Logon (email, lastname, firstname, password, userid)";
comm.CommandText += " values (@email, @lastname, @firstname, @password, @userid)";
comm.Parameters.AddWithValue("@email", txtemail.Text);
comm.Parameters.AddWithValue("@lastname", txtLname.Text);
comm.Parameters.AddWithValue("@firstname", txtfname.Text);
comm.Parameters.AddWithValue("@password", txtpassword.Text);
comm.Parameters.AddWithValue("@userid", txtuserid.Text);
if (!dbLayer.isuseralreadyinthedbornot(txtuserid.Text, connectionstring))
{
comm.ExecuteNonQuery();
}
else
{
MessageBox.Show("You Must enter another userid, that one is in use!");
}
conn.Close();
conn.Dispose();
}
public static bool Delete(System.Data.SqlClient.SqlConnection connection, System.Data.DataRow row, out string message)
{
bool done = false;
message = "";
try
{
connection.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
string sQuery = "DELETE FROM " + Categories.Table + "\n" +
" WHERE CategoryID = @CategoryID";
cmd.Parameters.AddWithValue("@CategoryID", row["CategoryID"]);
cmd.Connection = connection;
cmd.CommandTimeout = 0;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sQuery;
cmd.ExecuteNonQuery();
connection.Close();
done = true;
}
catch (System.Exception ex)
{
message = ex.Message;
}
finally
{
if (connection.State == System.Data.ConnectionState.Open) connection.Close();
}
return done;
}
public void InsertError(Exception err, int refId)
{
string conn = "Data Source=diyfe.org;Initial Catalog=MLB;Persist Security Info=True;User ID=jbt411;Password=ZigZag15";
string queryString = "INSERT INTO [MLB].[dbo].[Error] ([RefId],[ErrorMethod],[ErrorText],[ErrorDate]) VALUES (@RefId,@ErrorMethod,@ErrorText,@ErrorDate)";
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(conn))
{
// Create the Command and Parameter objects.
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@RefId", refId);
if (err.Source == null)
{ err.Source = "none"; }
command.Parameters.AddWithValue("@ErrorMethod", err.Source);
command.Parameters.AddWithValue("@ErrorText", "Excpetion: " + err.Message + "</br>Inner Excpetion: " + err.InnerException);
command.Parameters.AddWithValue("@ErrorDate", DateTime.Now);
try
{
var email = EmailMessageFactory.GetErrorEmail(err);
var result = EmailClient.SendEmail(email);
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
var email = EmailMessageFactory.GetErrorEmail(ex);
var result = EmailClient.SendEmail(email);
}
}
}
/// <summary>
/// �������� ������ � �� � ���������� �����
/// </summary>
/// <param name="connection">��������� � ��</param>
/// <param name="row">������</param>
/// <param name="message">�������� ��������� �� ������, ���� ����� ���������� ����</param>
/// <returns>����� ���������� ������, ���� �� �������� ������; ���� - � ��������� ������</returns>
public static bool Delete(System.Data.SqlClient.SqlConnection connection, System.Data.DataRow row, /*byte[] image,*/ out string message)
{
bool done = false;
message = "";
try{
connection.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
string sQuery = "IF EXISTS (SELECT DiscountCard\n" +
" FROM Purchases.Receipts\n" +
" WHERE Purchases.Receipts.DiscountCard = @CardID ) \n" +
" UPDATE Purchases.DiscountCards\n" +
" SET Expired = GETDATE()\n" +
" WHERE CardID = @CardID\n" +
"ELSE\n" +
" DELETE\n" +
" FROM Purchases.DiscountCards\n" +
" WHERE CardID = @CardID";
cmd.Parameters.AddWithValue("@CardID", row["CardID"]);
cmd.Connection = connection;
cmd.CommandTimeout = 0;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sQuery;
cmd.ExecuteNonQuery();
connection.Close();
done = true;
}catch (System.Exception ex){
message = ex.Message;
}finally{
if (connection.State == System.Data.ConnectionState.Open) connection.Close();
}
return done;
}
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
String sqlstring;
// sqlstring = "insert into sys_user values ((select max(sysuser_id)+1 from sys_user),'" +
// CreateUserWizard1.UserName + "','" + CreateUserWizard1.Password + "','1', GETDATE(), 3);";
//SQL 2005 version
sqlstring = "insert into sys_user select max(sysuser_id)+1, '" +
CreateUserWizard1.UserName + "','" + CreateUserWizard1.Password +
"', GETDATE(), (Select top 1 sysrole_id from sys_role where sysrole_name='Applicant') from sys_user ";
// create a connection with sqldatabase
//System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(
// " Data Source=SD;Initial Catalog=SRS;User ID=sa;Password=Ab123456;Connect Timeout=10;TrustServerCertificate=True ");
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(
WebConfigurationManager.ConnectionStrings["SRSDB"].ConnectionString);
// create a sql command which will user connection string and your select statement string
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring, con);
// create a sqldatabase reader which will execute the above command to get the values from sqldatabase
try
{
con.Open();
if (comm.ExecuteNonQuery() > 0)
{
}
}
finally
{
con.Close();
}
}
public void Send_Process(string process)
{
Conexion conn = new Conexion();
conn.Begin_conexion();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.CommandText = process;
command.Connection = conn.conexion;
int rows_mod = command.ExecuteNonQuery();
conn.conexion.Close();
}
public void checkItemIfExists(object itemName, System.Data.SqlClient.SqlConnection connection)
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Parameters.AddWithValue("@itemname", itemName);
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT itemtable WHERE Item_Name = @itemname";
cmd.Connection = connection;
cmd.ExecuteNonQuery();
}
protected void cmdAddUser_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["HuberTrackerConnection"].ToString();
conn.ConnectionString = connectionstring;
conn.Open();
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
comm.Connection = conn;
string sql = "select userid from huber_logon where userid = @userid";
comm.CommandText = sql;
comm.Parameters.AddWithValue("@userid", txtUserName.Text);
object result = comm.ExecuteScalar();
if (result != null)
{
//that userid exists! ABORT! (and tell the user)
lblError.Text = "That username is already in use.";
lblError.Visible = true;
}
else
{
sql = "insert into huber_logon (userid, password, firstname, lastname, email) values (@userid, @password, @firstname, @lastname, @email)";
comm.CommandText = sql;
comm.Parameters.Clear();
comm.Parameters.AddWithValue("@userid", txtUserName.Text);
comm.Parameters.AddWithValue("@password", txtPassword.Text);
comm.Parameters.AddWithValue("@firstname", txtFirstName.Text);
comm.Parameters.AddWithValue("@lastname", txtLastName.Text);
comm.Parameters.AddWithValue("@email", txtEmail.Text);
int numrowsaffected;
numrowsaffected = comm.ExecuteNonQuery();
if (numrowsaffected == 1)
{
// lblError.Text = "User Added!";
lblError.Visible = true;
ClearFields();
lblError.Text = EmailThatUser(txtEmail.Text);
lblError.Text += "cockroach";
}
else
{
lblError.Text = "There was a problem with the user!";
lblError.Visible = true;
}
}
conn.Close();
}
public static void agregarGrupoPredeterminado(blc.Grupo grupo, dal.DT dt)
{
System.Data.SqlClient.SqlCommand Comando;
string query = "INSERT INTO Grupo VALUES('" + grupo.Codigo + "','" + grupo.Nombre +
"','" + grupo.PermisoGestion + "','" + grupo.PermisoCasos + "','" + grupo.PermisoClientes +
"','" + grupo.PermisoTestigos + "','" + grupo.PermisoInventario + "','" + grupo.PermisoEvidencia + "','" + grupo.PermisoReportes + "','" + grupo.PermisoConfiguracion + "')";
Comando = new System.Data.SqlClient.SqlCommand(query);
Comando.Connection = dt.Connecion;
Comando.Connection.Open();
Comando.ExecuteNonQuery();
Comando.Connection.Close();
}
public static void agregarTestigoACaso(blc.Caso caso, int testigo)
{
System.Data.SqlClient.SqlCommand Comando;
string query;
query = "INSERT INTO testigosCaso VALUES('" + caso.Testigos[testigo].ID + "','" + caso.NumAccion + "')";
Comando = new System.Data.SqlClient.SqlCommand(query);
Comando.Connection = Program.dt.Connecion;
Comando.Connection.Open();
Comando.ExecuteNonQuery();
Comando.Connection.Close();
}
public static void agregarUsuarioPredeterminado(blc.Usuario usuario, dal.DT dt)
{
System.Data.SqlClient.SqlCommand Comando;
string query;
query = "INSERT INTO Usuario VALUES('" + usuario.Codigo + "','" + usuario.Password + "','" + usuario.Nombre + "','" + usuario.Apellido +
"','" + usuario.Celular + "','" + usuario.Email + "','" + usuario.Grupo.Codigo + "','" + usuario.Superusuario + "')";
Comando = new System.Data.SqlClient.SqlCommand(query);
Comando.Connection = dt.Connecion;
Comando.Connection.Open();
Comando.ExecuteNonQuery();
Comando.Connection.Close();
}
public static void insertarConfiguracion(string nombre, dal.DT dt)
{
System.Data.SqlClient.SqlCommand Comando;
string query;
query = "INSERT INTO Bufete VALUES('" + nombre + "', 'Español', 'Papiro', '')";
Comando = new System.Data.SqlClient.SqlCommand(query);
Comando.Connection = dt.Connecion;
Comando.Connection.Open();
Comando.ExecuteNonQuery();
Comando.Connection.Close();
}
public static void agregarArticulo(blc.Articulo articulo)
{
System.Data.SqlClient.SqlCommand Comando;
string query;
query = "INSERT INTO Articulo VALUES('" + articulo.Codigo + "','" + articulo.Nombre + "','" + articulo.Costo + "','" +
articulo.Ubicacion + "')";
Comando = new System.Data.SqlClient.SqlCommand(query);
Comando.Connection = Program.dt.Connecion;
Comando.Connection.Open();
Comando.ExecuteNonQuery();
Comando.Connection.Close();
}
private void Context_BeginRequest(object sender, EventArgs e)
{
try
{
HttpApplication context = (HttpApplication)sender;
System.Collections.Specialized.NameValueCollection ServerVariables = HttpContext.Current.Request.ServerVariables;
// 网页地址
string absoluteUri = context.Request.Url.AbsoluteUri;
if (!absoluteUri.EndsWith(".aspx", StringComparison.OrdinalIgnoreCase) &&
!absoluteUri.EndsWith(".htm", StringComparison.OrdinalIgnoreCase) &&
!absoluteUri.EndsWith(".html", StringComparison.OrdinalIgnoreCase) &&
!absoluteUri.EndsWith(".asp", StringComparison.OrdinalIgnoreCase))
{
return;
}
string referer = context.Request.ServerVariables["HTTP_REFERER"] == null ? string.Empty : context.Request.ServerVariables["HTTP_REFERER"].ToString();
string useragent = ServerVariables["HTTP_USER_AGENT"].ToString();
// ip地址
string ipAddress = ServerVariables["REMOTE_ADDR"].ToString();
string cmdText = string.Format("insert into UserAgentLog(AbsoluteUri, Referer, Useragent, IPAddress) values('{0}', '{1}', '{2}', '{3}')",
absoluteUri, referer, useragent, ipAddress);
// 添加统计记录
ConnectionStringsSection connectionStringsSection = ConfigurationManager.GetSection("connectionStrings") as ConnectionStringsSection;
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection();
sqlConnection.ConnectionString = connectionStringsSection.ConnectionStrings[1].ConnectionString;
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(cmdText, sqlConnection);
sqlConnection.Open();
int iExecuteNonQuery = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
catch (Exception ex)
{
string path = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Substring(8).ToLower();
int length = path.LastIndexOf("/bin/"); // 截取掉DLL的文件名，得到DLL当前的路径
path = path.Substring(0, length).Replace("/", "\\") + "\\Logs\\";
if (!System.IO.Directory.Exists(path)) System.IO.Directory.CreateDirectory(path);
path += DateTime.Today.ToShortDateString() + ".log";
using (StreamWriter sw = new StreamWriter(path, true, Encoding.UTF8))
{
//清除 回车符；将双引号修改为 \"
string s = ex.Message.Replace("\r", "").Replace("\n", "").Replace("\"", "\\\"");
sw.WriteLine(DateTime.Now + " " + s);
}
}
}
public static void agregarEvidencia(blc.Evidencia evidencia)
{
System.Data.SqlClient.SqlCommand Comando;
string query;
query = "INSERT INTO Evidencia VALUES('" + evidencia.Codigo + "','" + evidencia.Descripcion + "','" + evidencia.Caso + "','" +
evidencia.Path + "')";
Comando = new System.Data.SqlClient.SqlCommand(query);
Comando.Connection = Program.dt.Connecion;
Comando.Connection.Open();
Comando.ExecuteNonQuery();
Comando.Connection.Close();
}
public void UpdateDBSimple(string sql, string connectionstring)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = connectionstring;
conn.Open();
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
comm.Connection = conn;
comm.CommandText = sql;
comm.ExecuteNonQuery();
conn.Close();
}
public void UpdateDBSimple(string sql, string connectionstring)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
//string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings[1].ToString();
conn.ConnectionString = connectionstring;
conn.Open();
// Console.WriteLine(conn.State);
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
comm.Connection = conn;
comm.CommandText = sql;
comm.ExecuteNonQuery();
conn.Close();
}
public static void agregarCaso(blc.Caso caso)
{
System.Data.SqlClient.SqlCommand Comando;
string query;
query = "INSERT INTO Caso VALUES('" + caso.NumAccion + "','" + caso.Accion + "','" + caso.Materia +
"','" + caso.Oficina + "','" + caso.Cliente.ID + "','" + "PARTE2" + "','" + caso.Observaciones + "')";
Comando = new System.Data.SqlClient.SqlCommand(query);
Comando.Connection = Program.dt.Connecion;
Comando.Connection.Open();
Comando.ExecuteNonQuery();
Comando.Connection.Close();
}
void ExecuteSQL(string SQL, string connectionString)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(SQL, conn);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
conn.Dispose();
}
}
public Boolean Delete_Job_Record(String jobNumber, DateTime timeStamp, System.Data.SqlClient.SqlTransaction trnEnvelope)
{
Boolean isSuccessful = true;
System.Data.DataTable myJob = new System.Data.DataTable();
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT * FROM Jobs WHERE JobNumber = '" + jobNumber + "'";
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
myJob.Load(rdrGet);
// Delete Associated Extra Services
strSQL = "DELETE FROM JobExtraServices WHERE JobId = " + Convert.ToInt32(myJob.Rows[0]["JobId"]).ToString();
System.Data.SqlClient.SqlCommand cmdDeleteS = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
cmdDeleteS.ExecuteNonQuery();
// Delete Assoication Parts
strSQL = "DELETE FROM JobDetails WHERE JobId = " + Convert.ToInt32(myJob.Rows[0]["JobId"]).ToString();
System.Data.SqlClient.SqlCommand cmdDeleteP = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
cmdDeleteP.ExecuteNonQuery();
// Delete Job
strSQL = "DELETE FROM Jobs WHERE JobId = " + Convert.ToInt32(myJob.Rows[0]["JobId"]).ToString();
System.Data.SqlClient.SqlCommand cmdDeleteJ = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
cmdDeleteJ.ExecuteNonQuery();
// Update Job Status Table
strSQL = "INSERT INTO JobStatusLog (";
strSQL = strSQL + "JobNumber, ";
strSQL = strSQL + "JobStatus, ";
strSQL = strSQL + "Updated, ";
strSQL = strSQL + "Source) VALUES (";
strSQL = strSQL + "'" + jobNumber + "', ";
strSQL = strSQL + "'Deferred', ";
strSQL = strSQL + "CONVERT(datetime, '" + DateTime.Now.ToString() + "', 103), ";
strSQL = strSQL + "'Factory')";
System.Data.SqlClient.SqlCommand cmdInsert = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
cmdInsert.ExecuteNonQuery();
}
else
{
isSuccessful = false;
ErrorMessage = "Delete Job Record - Job Record Not Found";
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
isSuccessful = false;
ErrorMessage = "Delete Job Record - " + ex.Message;
}
return(isSuccessful);
}
public static void ExecuteSQLFile(String sqlFileName, string DatabaseName)
{
System.Data.SqlClient.SqlConnection mySqlConnection = null;
try
{
var config = new Utility.SiteConfig();
string connStr = config.ConnString;
mySqlConnection = new System.Data.SqlClient.SqlConnection(connStr);
System.Data.SqlClient.SqlCommand Command = mySqlConnection.CreateCommand();
Command.Connection.Open();
Command.Connection.ChangeDatabase(DatabaseName);
using (FileStream stream = new FileStream(sqlFileName, FileMode.Open, FileAccess.ReadWrite))
{
StreamReader reader = new StreamReader(stream, Encoding.Default);
StringBuilder builder = new StringBuilder();
String strLine = "";
char spaceChar = ' ';
string sprit = "/", whiffletree = "-";
while ((strLine = reader.ReadLine()) != null)
{
// 文件结束
if (strLine == null)
{
break;
}
// 跳过注释行
if (strLine.StartsWith(sprit) || strLine.StartsWith(whiffletree))
{
continue;
}
// 去除右边空格
strLine = strLine.TrimEnd(spaceChar);
if (strLine.Trim().ToUpper() != @"GO")
{
builder.AppendLine(strLine);
}
else
{
Command.CommandText = builder.ToString().TrimEnd(' ');
try
{
Command.ExecuteNonQuery();
}
catch (Exception ex)
{
LogHelper.WriteError("更新数据库失败", DatabaseName, ex);
}
builder.Remove(0, builder.Length);
}
}
stream.Dispose();
}
}
catch (Exception ex)
{
}
finally
{
if (mySqlConnection != null && mySqlConnection.State != ConnectionState.Closed)
{
mySqlConnection.Close();
}
}
}
public Boolean Update_Job_Status(String jobNumber, String jobStatus, DateTime timeStamp, System.Data.SqlClient.SqlTransaction trnEnvelope)
{
Boolean isSuccessful = true;
ErrorMessage = string.Empty;
try
{
if (Job_Already_Completed(jobNumber, trnEnvelope) == true)
{
String strSQL = "INSERT INTO JobStatusLog (";
strSQL = strSQL + "JobNumber, ";
strSQL = strSQL + "JobStatus, ";
strSQL = strSQL + "Updated, ";
strSQL = strSQL + "Source) VALUES (";
strSQL = strSQL + "'" + jobNumber + "', ";
strSQL = strSQL + "'**********', ";
strSQL = strSQL + "CONVERT(datetime, '" + DateTime.Now.ToString() + "', 103), ";
strSQL = strSQL + "'Factory')";
System.Data.SqlClient.SqlCommand cmdInsert = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
cmdInsert.ExecuteNonQuery();
}
else
{
String strSQL = "UPDATE Jobs SET ";
if (jobStatus == "Processed")
{
strSQL = strSQL + "CompletionDate = CONVERT(datetime, '" + timeStamp.ToString() + "', 103), ";
}
strSQL = strSQL + "JobStatus = '" + jobStatus + "' ";
strSQL = strSQL + "WHERE JobNumber = '" + jobNumber + "'";
System.Data.SqlClient.SqlCommand cmdUpdate = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
if (cmdUpdate.ExecuteNonQuery() != 1)
{
isSuccessful = false;
ErrorMessage = "Update Job Status - More than one record would be updated !";
}
else
{
strSQL = "INSERT INTO JobStatusLog (";
strSQL = strSQL + "JobNumber, ";
strSQL = strSQL + "JobStatus, ";
strSQL = strSQL + "Updated, ";
strSQL = strSQL + "Source) VALUES (";
strSQL = strSQL + "'" + jobNumber + "', ";
strSQL = strSQL + "'" + jobStatus + "', ";
strSQL = strSQL + "CONVERT(datetime, '" + DateTime.Now.ToString() + "', 103), ";
strSQL = strSQL + "'Factory')";
System.Data.SqlClient.SqlCommand cmdInsert = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
cmdInsert.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
isSuccessful = false;
ErrorMessage = "Update Job Status - " + ex.Message;
}
return(isSuccessful);
}
public static object SqlExecute(
string connectionStringName,
string storedProcedureName,
System
.Collections
.Generic
.Dictionary <string, object> parameters,
bool scalar)
{
object result = null;
using (System
.Data
.SqlClient
.SqlConnection connection =
new System.Data.SqlClient.SqlConnection(
string.IsNullOrWhiteSpace(connectionStringName)
? _connectionString
: connectionStringName.getConnectionString()))
if (connection != null)
{
using (System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand()
{
CommandText = storedProcedureName,
CommandType = System
.Data
.CommandType
.StoredProcedure,
Connection = connection
})
if (command != null)
{
if (parameters != null)
{
foreach (System
.Collections
.Generic
.KeyValuePair <string, object>
pair in parameters)
{
command.Parameters.AddWithValue(
pair.Key, pair.Value);
}
}
command.Connection.Open();
result = scalar
? command.ExecuteScalar()
: command.ExecuteNonQuery();
if (command.Connection.State ==
System.Data.ConnectionState.Open)
{
command.Connection.Close();
}
}
}
return(result);
}
private void Signals(string Tiker)
{
int vse, bpu_count, bsu_count;
double OpenPrice = 0;
string Signal = "";
bool inTrade = true;
double StopLoss = 0;
double TakeProfit = 0;
var Trade = (from c in db.Table where c.Tiker == Tiker orderby c.Id descending select c.Trade).ToArray();
var ID = (from c in db.Table where c.Tiker == Tiker orderby c.Id descending select c.Id).ToArray();
var H = (from c in db.Table where c.Tiker == Tiker orderby c.Id descending select c.High).ToArray();
var C = (from c in db.Table where c.Tiker == Tiker orderby c.DateTime descending select c.Close).ToArray();
var L = (from c in db.Table where c.Tiker == Tiker orderby c.Id descending select c.Low).ToArray();
var MailSent = (from c in db.Table where c.Tiker == Tiker orderby c.Id descending select c.MailSent).ToArray();
// var BSU = (from c in db.Table where c.Tiker == Tiker orderby c.DateTime descending select c.BSU).ToArray();
//var BPU = (from c in db.Table where c.Tiker == Tiker orderby c.DateTime descending select c.BPU).ToArray();
vse = ID.Count() - 1;
if (checkBox1.Checked == true)
{
bpu_count = ID.Count() - 1;
}
else
{
bpu_count = 4;
}
for (int k = 0; k < bpu_count; k++)
{
if (Trade.GetValue(k).ToString().TrimEnd() != "")
{
if (Trade.GetValue(k).ToString().TrimEnd() != "Sell" && Trade.GetValue(k).ToString().TrimEnd() != "Buy")
{
inTrade = false;
label20.Text = "Ждем сигнал. Последний " + Trade.GetValue(k).ToString().TrimEnd();
k = bpu_count;
}
else
{
inTrade = true;
label20.Text = "В сделке " + Trade.GetValue(k).ToString().TrimEnd();
k = bpu_count;
}
}
}
for (int k = 0; k < bpu_count; k++)
{
if (Convert.ToDouble(H.GetValue(k)) == Convert.ToDouble(H.GetValue(k + 1)))
{
for (int p = k + 2; p < vse; p++)
{
if ((p - k) > 120)
{
p = vse;
}
if (Convert.ToDouble(H.GetValue(k)) == Convert.ToDouble(H.GetValue(p)))
{
if (k < 50 && MailSent.GetValue(k).ToString().TrimEnd() != "1")
{
Signal = "down";
StopLoss = Convert.ToDouble(H.GetValue(k)) * (1 + Convert.ToDouble(textBox1.Text) / 100);
OpenPrice = Convert.ToDouble(C.GetValue(k));
TakeProfit = Convert.ToDouble(C.GetValue(k)) * (1 - Convert.ToDouble(textBox2.Text) / 100);
}
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE [Table] SET BPU = 'down' WHERE id='" + ID.GetValue(k + 1).ToString() + "';" + "UPDATE [Table] SET MailSent = '1' WHERE id='" + ID.GetValue(k).ToString() + "';";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
break;
}
}
}
if (Convert.ToDouble(L.GetValue(k)) == Convert.ToDouble(L.GetValue(k + 1)))
{
for (int p = k + 2; p < vse; p++)
{
if ((p - k) > 120)
{
p = vse;
}
if (Convert.ToDouble(L.GetValue(k)) == Convert.ToDouble(L.GetValue(p)))
{
if (k < 50 && MailSent.GetValue(k).ToString().TrimEnd() != "1")
{
Signal = "up";
StopLoss = Convert.ToDouble(L.GetValue(k)) * (1 - Convert.ToDouble(textBox1.Text) / 100);
OpenPrice = Convert.ToDouble(C.GetValue(k));
TakeProfit = Convert.ToDouble(C.GetValue(k)) * (1 + Convert.ToDouble(textBox2.Text) / 100);
}
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE [Table] SET BPU = 'up' WHERE id='" + ID.GetValue(k + 1).ToString() + "';" + "UPDATE [Table] SET MailSent = '1' WHERE id='" + ID.GetValue(k).ToString() + "';";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
break;
}
}
}
}
////
if (Signal != "")
{
string TRANS = String.Format("{0:1ddHHmmss}", DateTime.ParseExact(Convert.ToString(DateTime.Now), "dd.MM.yyyy H:mm:ss", null));
string SLTRANS = String.Format("{0:2HHddmmss}", DateTime.ParseExact(Convert.ToString(DateTime.Now), "dd.MM.yyyy H:mm:ss", null));
string s = "";
StopLoss = Math.Round(StopLoss / 10) * 10;
TakeProfit = Math.Round(TakeProfit / 10) * 10;
if (Signal == "down")
{
if (checkBox4.Checked == false && inTrade == false)
{
SendOrder("RIU6", "S", "1", OpenPrice, TRANS);
SendSLOrder("RIU6", "B", "1", StopLoss, TakeProfit, SLTRANS);
}
s = Convert.ToString(DateTime.Now) + " " + " Сигнал к продаже: " + OpenPrice.ToString() + " Стоп: " + (Math.Round(StopLoss / 10) * 10).ToString() + " Тэйк: " + (Math.Round(TakeProfit / 10) * 10).ToString();
}
if (Signal == "up")
{
if (checkBox4.Checked == false && inTrade == false)
{
SendOrder("RIU6", "B", "1", OpenPrice, TRANS);
SendSLOrder("RIU6", "S", "1", StopLoss, TakeProfit, SLTRANS);
}
s = Convert.ToString(DateTime.Now) + " " + " Сигнал к покупке: " + OpenPrice.ToString() + " Стоп: " + (Math.Round(StopLoss / 10) * 10).ToString() + " Тэйк: " + (Math.Round(TakeProfit / 10) * 10).ToString();
}
SmtpClient Smtp = new SmtpClient("smtp.mail.ru", 25);
Smtp.EnableSsl = true;
Smtp.UseDefaultCredentials = false;
Smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
Smtp.DeliveryFormat = SmtpDeliveryFormat.SevenBit;
Smtp.Credentials = new NetworkCredential("*****@*****.**", "Sapromat1");
MailMessage Message = new MailMessage("*****@*****.**", "*****@*****.**", "Сигнал ", s);
Smtp.Send(Message);//отправка
}
}
private void btnAccept_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlTransaction sqlta;
if (TextBoxTAC.Text.Trim() == "")
{
MessageBox.Show("please input TAC code");
return;
}
switch (iStyle)
{
case 0: //增加
sqlConn.Open();
sqlComm.CommandText = "SELECT [TAC Code], [Product Code], [Indentor Code] FROM TAC WHERE ([TAC Code] = N'" + TextBoxTAC.Text.Trim() + "')";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
MessageBox.Show("TAC code" + TextBoxTAC.Text.Trim() + "duplicate，code is：" + sqldr.GetValue(1).ToString() + " ," + sqldr.GetValue(2).ToString());
sqldr.Close();
sqlConn.Close();
break;
}
sqldr.Close();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//得到ID号
sqlComm.CommandText = "INSERT INTO TAC ([TAC Code], [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Init Number]) VALUES (N'" + TextBoxTAC.Text.Trim() + "', " + iProduct.ToString() + ", N'" + sPCode + "', " + iIndentor.ToString() + ", N'" + sICode + "', " + numericUpDownNum.Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "SELECT @@IDENTITY";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
iID = Convert.ToInt32(sqldr.GetValue(0).ToString());
sqldr.Close();
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("error：" + ex.Message.ToString(), "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
MessageBox.Show("add TAC CODE finished", "infomation", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
break;
case 1: //修改
sqlConn.Open();
//查重
sqlComm.CommandText = "SELECT [TAC Code], [Product Code], [Indentor Code] FROM TAC WHERE ([TAC Code] = N'" + TextBoxTAC.Text.Trim() + "' AND ID <> " + iID.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
MessageBox.Show("TAC code" + TextBoxTAC.Text.Trim() + "duplicate，code is：" + sqldr.GetValue(1).ToString() + " ," + sqldr.GetValue(2).ToString());
sqldr.Close();
sqlConn.Close();
break;
}
sqldr.Close();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
sqlComm.CommandText = "UPDATE TAC SET [TAC Code] = N'" + TextBoxTAC.Text.Trim() + "' WHERE (ID = " + iID.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE acquire SET [TAC Code] = N'" + TextBoxTAC.Text.Trim() + "' WHERE ([TAC ID] = " + iID.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE actual SET [TAC Code] = N'" + TextBoxTAC.Text.Trim() + "' WHERE ([TAC ID] = " + iID.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("error：" + ex.Message.ToString(), "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
MessageBox.Show("edit TAC CODE finished", "infomation", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
break;
}
}
// Приемник события OnExchangeData
private void DDEInitializeHandler_ExchangeData(object sender, DataEventArgs e)
{
string ss = String.Empty;
// Формируем определение таблицы биржевых данных
ExchangeDataTable dataTable = e.exchangeDataTable;
//ExchangeDataCell[,] dataCell = e.exchangeDataTable.ExchangeDataCells;
portion++;
// string LastCandleBase = (from c in db.Table where c.Tiker == dataTable.TopicName.ToString() orderby c.DateTime descending select c.Id).Max().ToString();
int CandlesCount = 0;
//label10.Text = LastCandleBase;
// label8.Text = counter.ToString();
Candles LastCandleFromQuik = new Candles();
LastCandleFromQuik.ID = dataTable.ExchangeDataCells[1, 0].DataCell.ToString() + dataTable.ExchangeDataCells[1, 1].DataCell.ToString() + dataTable.TopicName.ToString();
label6.Text = LastCandleFromQuik.ID;
if (checkBox3.Checked == false)
{
CandlesCount = 1;
}
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
// Ну и выводим полученную от терминала Quik информацию
for (int r = 0; r < dataTable.RowsLength; r++)
{
if (dataTable.TopicName.ToString().Contains("candle") == true)
{
string TempID = dataTable.ExchangeDataCells[r, 0].DataCell.ToString() + dataTable.ExchangeDataCells[r, 1].DataCell.ToString() + dataTable.TopicName.ToString().Replace("candle", "");
string TempTiker = dataTable.TopicName.ToString().Replace("candle", "");
string TempTime = dataTable.ExchangeDataCells[r, 1].DataCell.ToString();
Single TempOpen = Convert.ToSingle(dataTable.ExchangeDataCells[r, 2].DataCell.ToString().Replace(".", ","));
Single TempClose = Convert.ToSingle(dataTable.ExchangeDataCells[r, 5].DataCell.ToString().Replace(".", ","));
Single TempHigh = Convert.ToSingle(dataTable.ExchangeDataCells[r, 3].DataCell.ToString().Replace(".", ","));
Single TempLow = Convert.ToSingle(dataTable.ExchangeDataCells[r, 4].DataCell.ToString().Replace(".", ","));
cmd.CommandText = "INSERT INTO Candles (ID, Ticker, CandleTime, CandleHigh, CandleLow, CandleOpen, CandleClose) SELECT '" + TempID + "','" + TempTiker + "','" + TempTime + "','" + TempHigh + "','" + TempLow + "','" + TempOpen + "','" + TempClose + "' WHERE NOT EXISTS (SELECT ID FROM Candles WHERE ID = '" + TempID + "');";
cmd.ExecuteNonQuery();
}
if (dataTable.TopicName.ToString().Contains("current") == true)
{
String TempChange, TempLast, TempOborot;
string TempTicker = dataTable.ExchangeDataCells[r, 0].DataCell.ToString();
TempChange = dataTable.ExchangeDataCells[r, 2].DataCell.ToString().Replace(",", ".");
TempLast = dataTable.ExchangeDataCells[r, 3].DataCell.ToString().Replace(",", ".");
TempOborot = dataTable.ExchangeDataCells[r, 4].DataCell.ToString().Replace(",", ".");
if (TempChange == "")
{
TempChange = "0.0";
}
if (TempLast == "")
{
TempLast = "0.0";
}
if (TempOborot == "")
{
TempOborot = "0.0";
}
cmd.CommandText = "INSERT INTO dbo.[Current] (Ticker, Change, Last_price, Oborot) SELECT '" + TempTicker + "'," + TempChange + "," + TempLast + "," + TempOborot + " WHERE NOT EXISTS (SELECT Ticker FROM dbo.[Current] WHERE Ticker = '" + TempTicker + "');";
cmd.CommandText = cmd.CommandText + "UPDATE dbo.[Current] SET Change = " + TempChange + ", Last_price = " + TempLast + ", Oborot = " + TempOborot + " where Ticker = '" + TempTicker + "' ;";
cmd.ExecuteNonQuery();
}
}
sqlConnection1.Close();
label5.Text = CandlesCount.ToString();
label4.Text = portion.ToString();
}
void PreserveSQLHistory(ref string _SQLTable)
{
string sHistorySuffix = "History";
if (_NumInstance > 1)
{
sHistorySuffix = "_History";
}
StringBuilder messageText = new StringBuilder();
try
{
SqlConnectionInfo connInfo = new SqlConnectionInfo(Properties.Settings.Default.SQLServer);
IDbConnection conn = connInfo.CreateConnectionObject();
conn.Open();
conn.ChangeDatabase(Properties.Settings.Default.SQLServerDatabase);
string sSQL = @"if object_id('[" + _SQLTable.Replace("'", "''") + @"]') is not null
select top 1 * from [" + _SQLTable.Replace("]", "]]") + "]";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sSQL, (System.Data.SqlClient.SqlConnection)conn);
cmd.CommandTimeout = 0;
System.Data.SqlClient.SqlDataReader datareader = cmd.ExecuteReader();
string sColumns = "";
for (int i = 0; i < datareader.FieldCount; i++)
{
if (!string.IsNullOrEmpty(sColumns))
{
sColumns += ", ";
}
sColumns += "[" + datareader.GetName(i) + "]";
}
datareader.Close();
if (sColumns != "")
{
sSQL = @"
if object_id('[" + _SQLTable.Replace("'", "''") + @"]') is not null
begin
if object_id('[" + _SQLTable.Replace("'", "''") + sHistorySuffix + @"]') is null
begin
select * into [" + _SQLTable.Replace("]", "]]") + sHistorySuffix + "] from [" + _SQLTable.Replace("]", "]]") + @"]
end
else
begin
SET IDENTITY_INSERT [" + _SQLTable.Replace("]", "]]") + sHistorySuffix + @"] ON
insert into [" + _SQLTable.Replace("]", "]]") + sHistorySuffix + "] (" + sColumns + @")
select * from [" + _SQLTable.Replace("]", "]]") + @"]
SET IDENTITY_INSERT [" + _SQLTable.Replace("]", "]]") + sHistorySuffix + @"] OFF
end
end
";
cmd.CommandText = sSQL;
int iRowsPreserved = cmd.ExecuteNonQuery();
messageText.Append(DateTime.Now.ToString() + ": Successfully preserved " + iRowsPreserved + " rows of history to table: " + _SQLTable + sHistorySuffix);
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Information);
}
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
messageText.Append(DateTime.Now.ToString() + ": Cannot preserve history of trace table. ").AppendLine();
messageText.Append("Error: " + ex.Message).AppendLine();
messageText.Append(ex.StackTrace).AppendLine();
while (ex.InnerException != null)
{
messageText.Append("INNER EXCEPTION: ");
messageText.Append(ex.InnerException.Message).AppendLine();
messageText.Append(ex.InnerException.StackTrace).AppendLine();
ex = ex.InnerException;
}
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Warning);
}
}
private static System.Data.SqlClient.SqlCommand CreateCommand(System.Data.SqlClient.SqlConnection con)
{
System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand("delete from dbo.records", con);
com.ExecuteNonQuery();
com = new System.Data.SqlClient.SqlCommand("insert into dbo.Records( " +
"[accessionyear],[technique],[mediacount],[edition],[totalpageviews],[groupcount],[people],[objectnumber],[colorcount],[lastupdate], " +
"[rank], [imagecount],[description],[dateoflastpageview],[dateoffirstpageview],[primaryimageurl] ,[colors] ,[dated],[contextualtextcount],[copyright],[period],[url],[provenance],[images],[publicationcount], " +
"[objectid], [culture],[verificationleveldescription], [standardreferencenumber],[worktypeid],[worktype] ,[department],[state],[markscount],[contact],[titlescount],[id],[title],[verificationlevel],[division],[style],[commentary],[relatedcount],[datebegin],[labeltext], " +
"[totaluniquepageviews],[dimensions],[exhibitioncount],[techniqueid],[dateend],[creditline],[imagepermissionlevel],[signed],[century],[classificationid],[medium],[peoplecount],[accesslevel],[classification])" +
"values(" +
"@accessionyear,@technique,@mediacount,@edition,@totalpageviews,@groupcount,@people,@objectnumber,@colorcount,@lastupdate, " +
"@rank, @imagecount,@description,@dateoflastpageview,@dateoffirstpageview,@primaryimageurl ,@colors ,@dated,@contextualtextcount,@copyright,@period,@url,@provenance,@images,@publicationcount, " +
"@objectid, @culture,@verificationleveldescription, @standardreferencenumber,@worktypeid,@worktype ,@department,@state,@markscount,@contact,@titlescount,@id,@title,@verificationlevel,@division,@style,@commentary,@relatedcount,@datebegin,@labeltext, " +
"@totaluniquepageviews,@dimensions,@exhibitioncount,@techniqueid,@dateend,@creditline,@imagepermissionlevel,@signed,@century,@classificationid,@medium,@peoplecount,@accesslevel,@classification)"
, con);
AddParameter(com, "@accessionyear", 0);
AddParameter(com, "@technique", "");
AddParameter(com, "@mediacount", 0);
AddParameter(com, "@edition", "");
AddParameter(com, "@totalpageviews", 0);
AddParameter(com, "@groupcount", 0);
AddParameter(com, "@people", "");
AddParameter(com, "@objectnumber", "");
AddParameter(com, "@colorcount", 0);
AddParameter(com, "@lastupdate", DateTime.Now);
AddParameter(com, "@rank", 0);
AddParameter(com, "@imagecount", 0);
AddParameter(com, "@description", "");
AddParameter(com, "@dateoflastpageview", DateTime.Now);
AddParameter(com, "@dateoffirstpageview", DateTime.Now);
AddParameter(com, "@primaryimageurl", "");
AddParameter(com, "@colors", "");
AddParameter(com, "@dated", 0);
AddParameter(com, "@contextualtextcount", 0);
AddParameter(com, "@copyright", "");
AddParameter(com, "@period", "");
AddParameter(com, "@url", "");
AddParameter(com, "@provenance", "");
AddParameter(com, "@images", "");
AddParameter(com, "@publicationcount", 0);
AddParameter(com, "@objectid", 0);
AddParameter(com, "@culture", "");
AddParameter(com, "@verificationleveldescription", "");
AddParameter(com, "@standardreferencenumber", "");
AddParameter(com, "@worktypeid", 0);
AddParameter(com, "@worktype", "");
AddParameter(com, "@department", "");
AddParameter(com, "@state", "");
AddParameter(com, "@markscount", 0);
AddParameter(com, "@contact", "");
AddParameter(com, "@titlescount", 0);
AddParameter(com, "@id", 1);
AddParameter(com, "@title", "");
AddParameter(com, "@verificationlevel", 0);
AddParameter(com, "@division", "");
AddParameter(com, "@style", "");
AddParameter(com, "@commentary", "");
AddParameter(com, "@relatedcount", 0);
AddParameter(com, "@datebegin", 0);
AddParameter(com, "@labeltext", "");
AddParameter(com, "@totaluniquepageviews", 0);
AddParameter(com, "@dimensions", "");
AddParameter(com, "@exhibitioncount", 0);
AddParameter(com, "@techniqueid", "");
AddParameter(com, "@dateend", 0);
AddParameter(com, "@creditline", "");
AddParameter(com, "@imagepermissionlevel", 0);
AddParameter(com, "@signed", "");
AddParameter(com, "@century", "");
AddParameter(com, "@classificationid", 0);
AddParameter(com, "@medium", "");
AddParameter(com, "@peoplecount", 0);
AddParameter(com, "@accesslevel", 0);
AddParameter(com, "@classification", "");
return(com);
}
private void btnAccept_Click(object sender, EventArgs e)
{
int i1 = 0, i2 = 0;
string strDateSYS = "";
System.Data.SqlClient.SqlTransaction sqlta;
if (!countAmount())
{
return;
}
switch (iStyle)
{
case 0: //增加
sqlConn.Open();
//查重
if (textBoxDWBH.Text.Trim() == "")
{
MessageBox.Show("请输入单位编号");
sqlConn.Close();
break;
}
sqlComm.CommandText = "SELECT ID, 单位名称 FROM 单位表 WHERE (单位编号 = '" + textBoxDWBH.Text.Trim() + "')";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
MessageBox.Show("单位编号" + textBoxDWBH.Text.Trim() + "重复，名称为：" + sqldr.GetValue(1).ToString());
sqldr.Close();
sqlConn.Close();
break;
}
sqldr.Close();
sqlComm.CommandText = "SELECT ID, 单位编号 FROM 单位表 WHERE (单位名称 = '" + textBoxDWMC.Text.Trim() + "')";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
if (MessageBox.Show("单位名称" + textBoxDWMC.Text.Trim() + "重复，编号为：" + sqldr.GetValue(1).ToString() + "，是否继续？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.No)
{
sqldr.Close();
sqlConn.Close();
break;
}
}
sqldr.Close();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//得到表单号
//得到服务器日期
sqlComm.CommandText = "SELECT GETDATE() AS 日期";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDateSYS = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
if (checkBoxSFJH.Checked)
{
i1 = 1;
}
else
{
i1 = 0;
}
if (checkBoxSFXS.Checked)
{
i2 = 1;
}
else
{
i2 = 0;
}
sqlComm.CommandText = "INSERT INTO 单位表 (单位编号, 单位名称, 助记码, 是否进货, 是否销售, 税号, 电话, 开户银行, 银行账号, 联系人, 地址, 地区名称, 行业名称, 传真, 邮编, 备注, 登录日期, 联系地址, 应付账款, 应收账款, BeActive, 收货人, 业务员, 到站名称, 部门ID,开票电话,收货电话) VALUES ('" + textBoxDWBH.Text.Trim() + "', N'" + textBoxDWMC.Text.Trim() + "', '" + textBoxZJM.Text.Trim() + "', " + i1.ToString() + ", " + i2.ToString() + ", N'" + textBoxSH.Text.Trim() + "', '" + textBoxDH.Text.Trim() + "', N'" + comboBoxKHYH.Text.Trim() + "', '" + textBoxYHZH.Text.Trim() + "', N'" + textBoxLXR.Text.Trim() + "', N'" + textBoxDZ.Text.Trim() + "', N'" + comboBoxDQMC.Text.Trim() + "', N'" + comboBoxHYMC.Text.Trim() + "', N'" + textBoxCZ.Text.Trim() + "', '" + textBoxYB.Text.Trim() + "', N'" + textBoxBZ.Text.Trim() + "', '" + strDateSYS + "', N'" + textBoxLXDZ.Text.Trim() + "', 0, 0, 1, N'" + textBoxSHR.Text.Trim() + "',N'" + comboBoxYWY.Text + "',N'" + comboBoxDZMC.Text.Trim() + "', " + comboBoxBM.SelectedValue.ToString() + ",N'" + textBoxKPDH.Text.Trim() + "',N'" + textBoxSHDH.Text.Trim() + "')";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "SELECT @@IDENTITY";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
iSelect = Convert.ToInt32(sqldr.GetValue(0).ToString());
sqldr.Close();
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
MessageBox.Show("增加成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
break;
case 1: //修改
sqlConn.Open();
//查重
if (textBoxDWBH.Text.Trim() == "")
{
MessageBox.Show("请输入单位编号");
sqlConn.Close();
break;
}
iSelect = Convert.ToInt32(dt.Rows[0][0].ToString());
sqlComm.CommandText = "SELECT ID, 单位名称 FROM 单位表 WHERE (单位编号 = '" + textBoxDWBH.Text.Trim() + "' AND ID <> " + iSelect.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
MessageBox.Show("单位编号" + textBoxDWBH.Text.Trim() + "重复，名称为：" + sqldr.GetValue(1).ToString());
sqldr.Close();
sqlConn.Close();
break;
}
sqldr.Close();
sqlComm.CommandText = "SELECT ID, 单位编号 FROM 单位表 WHERE (单位名称 = '" + textBoxDWMC.Text.Trim() + "' AND ID <> " + iSelect.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
if (MessageBox.Show("单位名称" + textBoxDWMC.Text.Trim() + "重复，编号为：" + sqldr.GetValue(1).ToString() + "，是否继续？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.No)
{
sqldr.Close();
sqlConn.Close();
break;
}
}
sqldr.Close();
//使用状态
sqlComm.CommandText = "SELECT 单位表.单位名称, 单据汇总视图.单据编号 FROM 单位表 INNER JOIN 单据汇总视图 ON 单位表.ID = 单据汇总视图.单位ID WHERE (单据汇总视图.BeActive = 1) AND (单位表.ID = " + iSelect.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
if (textBoxDWMC.Text.Trim() != sqldr.GetValue(0).ToString())
{
MessageBox.Show("该单位已有单据保存，不可更改单位名称：" + sqldr.GetValue(0).ToString() + "。单据编号（示例）：" + sqldr.GetValue(1).ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
textBoxDWMC.Text = sqldr.GetValue(0).ToString();
}
sqldr.Close();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//得到表单号
//得到服务器日期
sqlComm.CommandText = "SELECT GETDATE() AS 日期";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDateSYS = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
if (checkBoxSFJH.Checked)
{
i1 = 1;
}
else
{
i1 = 0;
}
if (checkBoxSFXS.Checked)
{
i2 = 1;
}
else
{
i2 = 0;
}
iSelect = Convert.ToInt32(dt.Rows[0][0].ToString());
sqlComm.CommandText = "UPDATE 单位表 SET 单位编号 = '" + textBoxDWBH.Text.Trim() + "', 单位名称 = N'" + textBoxDWMC.Text.Trim() + "', 助记码 = '" + textBoxZJM.Text.Trim() + "', 是否进货 = " + i1.ToString() + ", 是否销售 = " + i2.ToString() + ", 税号 = N'" + textBoxSH.Text.Trim() + "', 电话 = '" + textBoxDH.Text.Trim() + "', 开户银行 = N'" + comboBoxKHYH.Text.Trim() + "', 银行账号 = '" + textBoxYHZH.Text.Trim() + "', 联系人 = N'" + textBoxLXR.Text.Trim() + "', 地址 = N'" + textBoxDZ.Text.Trim() + "', 地区名称 = N'" + comboBoxDQMC.Text.Trim() + "', 行业名称 = N'" + comboBoxHYMC.Text.Trim() + "', 传真 = N'" + textBoxCZ.Text.Trim() + "', 邮编 = '" + textBoxYB.Text.Trim() + "', 备注 = N'" + textBoxBZ.Text.Trim() + "', 登录日期 = '" + dateTimePickerDLRQ.Value.ToShortDateString() + "', 联系地址 = N'" + textBoxLXDZ.Text.Trim() + "',收货人=N'" + textBoxSHR.Text.Trim() + "', 业务员=N'" + comboBoxYWY.Text.Trim() + "', 到站名称=N'" + comboBoxDZMC.Text.Trim() + "', 部门ID = " + comboBoxBM.SelectedValue.ToString() + ", 开票电话=N'" + textBoxKPDH.Text.Trim() + "', 收货电话=N'" + textBoxSHDH.Text.Trim() + "' WHERE (ID = " + iSelect + ")";
sqlComm.ExecuteNonQuery();
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
MessageBox.Show("修改成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
break;
default:
break;
}
}
public int ExecuteNonQuery()
{
return(comm.ExecuteNonQuery());
}
public static int Execute()
{
return(Command.ExecuteNonQuery());
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
int i, j = 0, k, iCount;
decimal dTemp = 0;
//保存完毕
if (isSaved)
{
MessageBox.Show("购进商品制单已经冲红完毕,单据号为：" + labelDJBH.Text, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
//
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
//查询入库
sqlComm.CommandText = "SELECT 单据编号 FROM 进货入库汇总表 WHERE (购进ID = " + intDJID.ToString() + ") AND (BeActive = 1)";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
while (sqldr.Read())
{
MessageBox.Show("已有入库记录,单据号为：" + sqldr.GetValue(0).ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
break;
}
sqldr.Close();
sqlConn.Close();
return;
}
sqldr.Close();
//发票记录
sqlComm.CommandText = "SELECT 发票号, ID FROM 购进商品制单表 WHERE (发票号 IS NOT NULL) AND (发票号 NOT LIKE N'不开票%') AND (ID = " + intDJID.ToString() + ") AND (发票号 NOT LIKE N'现金不开票%')";
sqldr = sqlComm.ExecuteReader();
bool b = false;
if (sqldr.HasRows)
{
while (sqldr.Read())
{
if (sqldr.GetValue(0).ToString().Trim() != "")
{
MessageBox.Show("已有发票记录,发票号为：" + sqldr.GetValue(0).ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
b = true;
}
break;
}
if (b)
{
sqldr.Close();
sqlConn.Close();
return;
}
}
sqldr.Close();
//得到上次结转时间
string sSCJZSJ = "";
sqlComm.CommandText = "SELECT 结算时间,ID FROM 结转汇总表 ORDER BY 结算时间 DESC";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
sSCJZSJ = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
if (sSCJZSJ == "") //没有结算
{
sqlComm.CommandText = "SELECT 开始时间 FROM 系统参数表";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
sSCJZSJ = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
}
//得到制单日期
string strDate1 = "";
sqlComm.CommandText = "SELECT 日期 from 购进商品制单表 WHERE (ID = " + intDJID.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDate1 = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
if (DateTime.Parse(strDate1) <= DateTime.Parse(sSCJZSJ)) //有转结记录
{
if (MessageBox.Show("制单后已有转结记录：" + sSCJZSJ + "，是否强行冲红？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) != DialogResult.Yes)
{
sqlConn.Close();
return;
}
}
//sqlConn.Close();
string strCount = "", strDateSYS = "", strKey = "AKP";
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
saveToolStripButton.Enabled = false;
try
{
//表单汇总
sqlComm.CommandText = "UPDATE 购进商品制单表 SET BeActive = 0 WHERE (ID = " + intDJID.ToString() + ")";
sqlComm.ExecuteNonQuery();
//修改合同未执行状态
//相关合同结束
if (iHT != 0)
{
sqlComm.CommandText = "UPDATE 采购合同表 SET 执行标记 = 0 WHERE (ID = " + iHT.ToString() + ")";
sqlComm.ExecuteNonQuery();
}
string sBMID = "NULL";
if (iBM != 0)
{
sBMID = iBM.ToString();
}
//得到服务器日期
sqlComm.CommandText = "SELECT GETDATE() AS 日期";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDateSYS = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
sqlComm.CommandText = "UPDATE 购进商品制单表 SET 冲红时间 = '" + strDateSYS + "' WHERE (ID = " + intDJID.ToString() + ")";
sqlComm.ExecuteNonQuery();
//单位历史纪录
dTemp = Convert.ToDecimal(labelSJJE.Text);
sqlComm.CommandText = "INSERT INTO 单位历史账表 (单位ID, 日期, 单据编号, 摘要, 购进未入库金额, 业务员ID, 冲抵单号, BeActive, 部门ID) VALUES ( " + iSupplyCompany.ToString() + ", '" + strDateSYS + "', N'" + labelDJBH.Text + "冲', N'购进商品制单冲红', -1*" + dTemp.ToString() + ", " + iYWY.ToString() + ", N'" + textBoxHTH.Text + "', 1, " + sBMID + ")";
sqlComm.ExecuteNonQuery();
//单据明细
for (i = 0; i < iRowCount; i++)
{
//sqlComm.CommandText = "DELETE FROM 购进商品制单明细表 WHERE (ID = " + dataGridViewGJSPZD.Rows[i].Cells[16].Value.ToString() + ")";
//sqlComm.ExecuteNonQuery();
//商品库房历史表
sqlComm.CommandText = "INSERT INTO 商品库房历史账表 (日期, 商品ID, 单位ID, 库房ID, 业务员ID, 单据编号, 摘要, 购进数量, 购进单价, 购进金额, BeActive, 部门ID) VALUES ('" + strDateSYS + "', " + dataGridViewGJSPZD.Rows[i].Cells[13].Value.ToString() + ", " + iSupplyCompany.ToString() + ", " + dataGridViewGJSPZD.Rows[i].Cells[14].Value.ToString() + ", " + iYWY.ToString() + ", N'" + labelDJBH.Text + "改', N'购进商品制单修改', -1*" + dataGridViewGJSPZD.Rows[i].Cells[7].Value.ToString() + ", " + dataGridViewGJSPZD.Rows[i].Cells[8].Value.ToString() + ", -1*" + dataGridViewGJSPZD.Rows[i].Cells[9].Value.ToString() + ", 1," + sBMID + ")";
sqlComm.ExecuteNonQuery();
//商品历史表
sqlComm.CommandText = "INSERT INTO 商品历史账表 (日期, 商品ID, 单位ID, 业务员ID, 单据编号, 摘要, 购进数量, 购进单价, 购进金额, BeActive, 部门ID) VALUES ('" + strDateSYS + "', " + dataGridViewGJSPZD.Rows[i].Cells[13].Value.ToString() + ", " + iSupplyCompany.ToString() + ", " + iYWY.ToString() + ", N'" + labelDJBH.Text + "改', N'购进商品制单修改', -1*" + dataGridViewGJSPZD.Rows[i].Cells[7].Value.ToString() + ", " + dataGridViewGJSPZD.Rows[i].Cells[8].Value.ToString() + ", -1*" + dataGridViewGJSPZD.Rows[i].Cells[9].Value.ToString() + ", 1," + sBMID + ")";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
saveToolStripButton.Enabled = true;
return;
}
finally
{
sqlConn.Close();
}
//MessageBox.Show("购进商品制单修改成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
isSaved = true;
if (MessageBox.Show("购进商品制单冲红成功，是否关闭单据窗口？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
this.Close();
}
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
int i, j;
decimal dKUL = 0;
decimal dKUL1 = 0;
textBoxHTH.Focus();
//保存完毕
if (isSaved)
{
MessageBox.Show("销售出库校对单已经保存,单据号为：" + labelDJBH.Text, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (toolStripStatusLabelMXJLS.Text == "0")
{
MessageBox.Show("没有销售出库校对单商品", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//if (MessageBox.Show("请检查销售销售出库校对单内容,是否继续保存？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
// return;
saveToolStripButton.Enabled = false;
string strCount = "", strDateSYS = "", strKey = "BCK";
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
//得到上次结转时间
string sSCJZSJ = "";
sqlComm.CommandText = "SELECT 结算时间,ID FROM 结转汇总表 ORDER BY 结算时间 DESC";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
sSCJZSJ = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
if (sSCJZSJ == "") //没有结算
{
sqlComm.CommandText = "SELECT 开始时间 FROM 系统参数表";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
sSCJZSJ = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
}
//得到制单日期
string strDate1 = "";
sqlComm.CommandText = "SELECT 日期 from 销售出库汇总表 WHERE (ID = " + intDJID.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDate1 = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
if (DateTime.Parse(strDate1) <= DateTime.Parse(sSCJZSJ)) //有转结记录
{
if (MessageBox.Show("制单后已有转结记录：" + sSCJZSJ + "，是否强行冲红？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) != DialogResult.Yes)
{
sqlConn.Close();
return;
}
}
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//表单汇总
string sBMID = "NULL";
if (iBM != 0)
{
sBMID = iBM.ToString();
}
sqlComm.CommandText = "UPDATE 销售出库汇总表 SET BeActive = 0 WHERE (ID = " + intDJID.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 销售出库汇总表 SET 冲红时间 = '" + strDateSYS + "' WHERE (ID = " + intDJID.ToString() + ")";
sqlComm.ExecuteNonQuery();
//未到货恢复
for (i = 0; i < dataGridViewDJMX.Rows.Count; i++)
{
sqlComm.CommandText = "UPDATE 销售商品制单明细表 SET 未出库数量 =未出库数量+" + dataGridViewDJMX.Rows[i].Cells[8].Value.ToString() + " WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[19].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
saveToolStripButton.Enabled = true;
return;
}
finally
{
sqlConn.Close();
}
checkRKView();
isSaved = true;
if (MessageBox.Show("销售出库校对单冲红成功，是否关闭单据窗口？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
this.Close();
}
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
//保存完毕
if (isSaved)
{
MessageBox.Show("应收账款结算单已经冲红,单据号为：" + labelDJBH.Text, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
int i, j, k;
string sTemp = "";
decimal dKUL = 0, dKCCBJ = 0, dZGJJ = 0, dZDJJ = 0, dZZJJ = 0;
decimal dKUL1 = 0, dKCCBJ1 = 0, dZGJJ1 = 0, dZDJJ1 = 0, dZZJJ1 = 0;
decimal dKCJE = 0, dKCJE1 = 0, dYSYE = 0, dYSYE1 = 0;
string strCommID, strKFID;
string sBMID = "NULL";
if (iBM != 0)
{
sBMID = iBM.ToString();
}
System.Data.SqlClient.SqlTransaction sqlta;
string strDateSYS;
cGetInformation.getSystemDateTime();
strDateSYS = cGetInformation.strSYSDATATIME;
sqlConn.Open();
//得到上次结转时间
string sSCJZSJ = "";
sqlComm.CommandText = "SELECT 结算时间,ID FROM 结转汇总表 ORDER BY 结算时间 DESC";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
sSCJZSJ = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
if (sSCJZSJ == "") //没有结算
{
sqlComm.CommandText = "SELECT 开始时间 FROM 系统参数表";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
sSCJZSJ = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
}
//得到制单日期
string strDate1 = "";
sqlComm.CommandText = "SELECT 日期 from 结算收款汇总表 WHERE (ID = " + intDJID.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDate1 = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
if (DateTime.Parse(strDate1) <= DateTime.Parse(sSCJZSJ)) //有转结记录
{
if (MessageBox.Show("制单后已有转结记录：" + sSCJZSJ + "，是否强行冲红？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) != DialogResult.Yes)
{
sqlConn.Close();
return;
}
}
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//表单汇总
sqlComm.CommandText = "UPDATE 结算收款汇总表 SET BeActive = 0 WHERE (ID = " + intDJID.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 结算收款汇总表 SET 冲红时间 = '" + strDateSYS + "' WHERE (ID = " + intDJID.ToString() + ")";
sqlComm.ExecuteNonQuery();
//单位应付账
sqlComm.CommandText = "SELECT 应收账款 FROM 单位表 WHERE (ID = " + iSupplyCompany.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
dKCJE = 0;
while (sqldr.Read())
{
dKCJE = Convert.ToDecimal(sqldr.GetValue(0).ToString());
}
sqldr.Close();
dKCJE1 = dKCJE + Convert.ToDecimal(labelSJJE.Text);
sqlComm.CommandText = "UPDATE 单位表 SET 应收账款 = " + dKCJE1.ToString() + " WHERE (ID = " + iSupplyCompany.ToString() + ")";
sqlComm.ExecuteNonQuery();
//单位历史纪录
sqlComm.CommandText = "INSERT INTO 单位历史账表 (单位ID, 日期, 单据编号, 摘要, 应收金额, 业务员ID, 冲抵单号, BeActive, 部门ID) VALUES (" + iSupplyCompany.ToString() + ", '" + strDateSYS + "', N'" + labelDJBH.Text + "冲', N'应收账款结算单冲红', " + dKCJE1.ToString() + ", " + iYWY.ToString() + ", N'" + labelDJBH.Text + "', 1," + sBMID + ")";
sqlComm.ExecuteNonQuery();
//明细更新
for (k = 0; k < dataGridViewDJMX.RowCount; k++)
{
sqlComm.CommandText = "SELECT ID, 勾兑方式, 勾兑ID, 单据编号, 已付款, BeActive FROM 结算收款勾兑表 WHERE (勾兑方式 = 1) AND (BeActive = 1) AND (付款ID = " + dataGridViewDJMX.Rows[k].Cells[0].Value.ToString() + ")";
if (dSet.Tables.Contains("勾兑表"))
{
dSet.Tables.Remove("勾兑表");
}
sqlDA.Fill(dSet, "勾兑表");
for (j = 0; j < dSet.Tables["勾兑表"].Rows.Count; j++)
{
//回退单据
sTemp = dSet.Tables["勾兑表"].Rows[j][3].ToString().Substring(0, 3);
strCommID = "0";
strKFID = "0";
switch (sTemp)
{
case "BKP":
sqlComm.CommandText = "UPDATE 销售商品制单明细表 SET 未付款金额 = 未付款金额 + " + dSet.Tables["勾兑表"].Rows[j][4].ToString() + ", 已付款金额 = 已付款金额 - " + dSet.Tables["勾兑表"].Rows[j][4].ToString() + " WHERE (ID = " + dSet.Tables["勾兑表"].Rows[j][2].ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 销售商品制单表 SET 未付款金额 = 未付款金额 + " + dSet.Tables["勾兑表"].Rows[j][4].ToString() + ", 已付款金额 = 已付款金额 - " + dSet.Tables["勾兑表"].Rows[j][4].ToString() + " WHERE (单据编号 = N'" + dSet.Tables["勾兑表"].Rows[j][3].ToString() + "')";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "SELECT 商品ID, 库房ID FROM 销售商品制单明细表 WHERE (ID = " + dSet.Tables["勾兑表"].Rows[j][2].ToString() + ")";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strCommID = sqldr.GetValue(0).ToString();
strKFID = sqldr.GetValue(1).ToString();
break;
}
sqldr.Close();
break;
case "BTH":
sqlComm.CommandText = "UPDATE 销售退出明细表 SET 未付款金额 = 未付款金额 - (" + dSet.Tables["勾兑表"].Rows[j][4].ToString() + "), 已付款金额 = 已付款金额 - (-1*" + dSet.Tables["勾兑表"].Rows[j][4].ToString() + ") WHERE (ID = " + dSet.Tables["勾兑表"].Rows[j][2].ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 销售退出汇总表 SET 未付款金额 = 未付款金额 - (" + dSet.Tables["勾兑表"].Rows[j][4].ToString() + "), 已付款金额 = 已付款金额 - (-1*" + dSet.Tables["勾兑表"].Rows[j][4].ToString() + ") WHERE (单据编号 = N'" + dSet.Tables["勾兑表"].Rows[j][3].ToString() + "')";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "SELECT 商品ID, 库房ID FROM 销售退出明细表 WHERE (ID = " + dSet.Tables["勾兑表"].Rows[j][2].ToString() + ")";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strCommID = sqldr.GetValue(0).ToString();
strKFID = sqldr.GetValue(1).ToString();
break;
}
sqldr.Close();
break;
case "BTB":
sqlComm.CommandText = "UPDATE 销售退补差价明细表 SET 未付款金额 = 未付款金额 + " + dSet.Tables["勾兑表"].Rows[j][4].ToString() + ", 已付款金额 = 已付款金额 - " + dSet.Tables["勾兑表"].Rows[j][4].ToString() + " WHERE (ID = " + dSet.Tables["勾兑表"].Rows[j][2].ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 销售退补差价汇总表 SET 未付款金额 = 未付款金额 + " + dSet.Tables["勾兑表"].Rows[j][4].ToString() + ", 已付款金额 = 已付款金额 - " + dSet.Tables["勾兑表"].Rows[j][4].ToString() + " WHERE (单据编号 = N'" + dSet.Tables["勾兑表"].Rows[j][3].ToString() + "')";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "SELECT 商品ID, 库房ID FROM 销售退补差价明细表 WHERE (ID = " + dSet.Tables["勾兑表"].Rows[j][2].ToString() + ")";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strCommID = sqldr.GetValue(0).ToString();
strKFID = sqldr.GetValue(1).ToString();
break;
}
sqldr.Close();
break;
}
//
sqlComm.CommandText = "UPDATE 结算收款勾兑表 SET BeActive = 0 WHERE (ID = " + dSet.Tables["勾兑表"].Rows[j][0].ToString() + ")";
sqlComm.ExecuteNonQuery();
//总库存
sqlComm.CommandText = "UPDATE 商品表 SET 应收金额 = 应收金额 +" + dSet.Tables["勾兑表"].Rows[j][4].ToString() + ", 已收金额 = 已收金额 -" + dSet.Tables["勾兑表"].Rows[j][4].ToString() + " WHERE (ID = " + strCommID + ")";
sqlComm.ExecuteNonQuery();
//分库存
sqlComm.CommandText = "UPDATE 库存表 SET 应收金额 = 应收金额 +" + dSet.Tables["勾兑表"].Rows[j][4].ToString() + ", 已收金额 = 已收金额 - " + dSet.Tables["勾兑表"].Rows[j][4].ToString() + " WHERE (库房ID = " + strKFID + ") AND (商品ID = " + strCommID + ") AND (BeActive = 1)";
sqlComm.ExecuteNonQuery();
//
}
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
//MessageBox.Show("应收账款结算单冲红成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
isSaved = true;
if (MessageBox.Show("应收账款结算单冲红成功，是否关闭单据窗口？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
this.Close();
}
}
public Boolean Update_Progress_Record(Int32 recordId, DateTime timeStamp, Int32 taskId, System.Data.SqlClient.SqlTransaction trnEnvelope, DateTime lineStart, DateTime lineStop)
{
Boolean isSuccessful = true;
ErrorMessage = string.Empty;
try
{
String strSQL = "UPDATE JobProgress SET ";
if (taskId == -1)
{
strSQL = strSQL + "ProgressLoadStart = NULL, ProgressLineStarted = NULL, ProgressLineStopped = NULL ";
}
else if (taskId == 1)
{
strSQL = strSQL + "ProgressLoadStart = CONVERT(datetime, '" + timeStamp.ToString() + "', 103), ProgressLineStarted = CONVERT(datetime, '" + lineStart.ToString() + "', 103), ProgressLineStopped = CONVERT(datetime, '" + lineStop.ToString() + "', 103) ";
}
else if (taskId == 2)
{
strSQL = strSQL + "ProgressLoadEnd = CONVERT(datetime, '" + timeStamp.ToString() + "', 103) ";
}
else if (taskId == -3)
{
strSQL = strSQL + "ProgressUnloadStart = NULL ";
}
else if (taskId == 3)
{
strSQL = strSQL + "ProgressUnloadStart = CONVERT(datetime, '" + timeStamp.ToString() + "', 103) ";
}
else if (taskId == 4)
{
strSQL = strSQL + "ProgressUnloadEnd = CONVERT(datetime, '" + timeStamp.ToString() + "', 103) ";
}
else if (taskId == 5)
{
strSQL = strSQL + "ProgressPacked = 'True' ";
}
else if (taskId == 6)
{
strSQL = strSQL + "ProgressUnloadEnd = CONVERT(datetime, '" + timeStamp.ToString() + "', 103), ProgressPacked = 'True', ProgressCompleted = 'True' ";
}
else if (taskId == 7)
{
strSQL = strSQL + "ProgressCompleted = 'True' ";
}
strSQL = strSQL + "WHERE ProgressId = " + recordId.ToString();
System.Data.SqlClient.SqlCommand cmdUpdate = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
if (cmdUpdate.ExecuteNonQuery() != 1)
{
isSuccessful = false;
ErrorMessage = "Update Progress Record - More than one record would be updated !";
}
}
catch (Exception ex)
{
isSuccessful = false;
ErrorMessage = "Update Progress Record - " + ex.Message;
}
return(isSuccessful);
}
/// <summary>
/// This method allows you to execute the [spD_tblCustomer] stored procedure.
/// </summary>
/// <param name="parameters">
/// Contains all the necessary information to execute correctly the stored procedure, i.e.
/// the database connection to use and all the necessary input parameters to be supplied
/// for this stored procedure execution. After the execution, this object will allow you
/// to retrieve back the stored procedure return value and all the output parameters.
/// </param>
/// <returns>True if the call was successful. Otherwise, it returns False.</returns>
public bool Execute(ref OlymarsDemo.DataClasses.Parameters.spD_tblCustomer parameters)
{
System.Data.SqlClient.SqlCommand sqlCommand = null;
System.Boolean returnStatus = false;
System.Boolean connectionMustBeClosed = true;
try {
ResetParameter(ref parameters);
parameters.internal_SetErrorSource(ErrorSource.ConnectionInitialization);
returnStatus = InitializeConnection(ref parameters, out sqlCommand, ref connectionMustBeClosed);
if (!returnStatus)
{
return(false);
}
parameters.internal_SetErrorSource(ErrorSource.ParametersSetting);
returnStatus = DeclareParameters(ref parameters, ref sqlCommand);
if (!returnStatus)
{
return(false);
}
parameters.internal_SetErrorSource(ErrorSource.QueryExecution);
sqlCommand.ExecuteNonQuery();
parameters.internal_SetErrorSource(ErrorSource.ParametersRetrieval);
returnStatus = RetrieveParameters(ref parameters, ref sqlCommand);
}
catch (System.Data.SqlClient.SqlException sqlException) {
parameters.internal_UpdateExceptionInformation(sqlException);
returnStatus = false;
if (this.throwExceptionOnExecute)
{
throw sqlException;
}
}
catch (System.Exception exception) {
parameters.internal_UpdateExceptionInformation(exception);
returnStatus = false;
parameters.internal_SetErrorSource(ErrorSource.Other);
if (this.throwExceptionOnExecute)
{
throw exception;
}
}
finally {
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
if (parameters.SqlTransaction == null)
{
if (this.sqlConnection != null && connectionMustBeClosed && this.sqlConnection.State == System.Data.ConnectionState.Open)
{
this.sqlConnection.Close();
this.sqlConnection.Dispose();
}
}
if (returnStatus)
{
parameters.internal_SetErrorSource(ErrorSource.NoError);
}
else
{
if (this.throwExceptionOnExecute)
{
if (parameters.SqlException != null)
{
throw parameters.SqlException;
}
else
{
throw parameters.OtherException;
}
}
}
}
return(returnStatus);
}
private void btnAccept_Click(object sender, EventArgs e)
{
int i;
int i1 = 0, i2 = 0;
string strDateSYS = "";
System.Data.SqlClient.SqlTransaction sqlta;
if (textBoxSPBH.Text.Trim() == "")
{
MessageBox.Show("输入类型错误，请输入商品编号", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (textBoxSPMC.Text.Trim() == "")
{
MessageBox.Show("输入类型错误，请输入商品名称", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (iClass == 0)
{
MessageBox.Show("输入类型错误，请选择商品类型", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
switch (iStyle)
{
case 0: //增加
sqlConn.Open();
//查重
if (textBoxSPBH.Text.Trim() == "")
{
MessageBox.Show("请输入商品编号");
sqlConn.Close();
break;
}
sqlComm.CommandText = "SELECT ID, 商品名称 FROM 商品表 WHERE (商品编号 = '" + textBoxSPBH.Text.Trim() + "')";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
MessageBox.Show("商品编号" + textBoxSPBH.Text.Trim() + "重复，名称为：" + sqldr.GetValue(1).ToString());
sqldr.Close();
sqlConn.Close();
break;
}
sqldr.Close();
sqlComm.CommandText = "SELECT ID, 商品编号 FROM 商品表 WHERE (商品名称 = '" + textBoxSPMC.Text.Trim() + "')";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
if (MessageBox.Show("商品名称" + textBoxSPMC.Text.Trim() + "重复，编号为：" + sqldr.GetValue(1).ToString() + "，是否继续？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.No)
{
sqldr.Close();
sqlConn.Close();
break;
}
}
sqldr.Close();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//得到服务器日期
sqlComm.CommandText = "SELECT GETDATE() AS 日期";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDateSYS = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
sqlComm.CommandText = "INSERT INTO 商品表 (商品编号, 商品名称, 助记码, 最小计量单位, 进价, 含税进价, 批发价, 含税批发价, 库存数量, 库存成本价, 库存金额, 库存件数, 最高进价, 最低进价, 最终进价, 结转数量, 结转件数, 结转金额, 结转单价, 登录日期, 库存上限, 库存下限, 合理库存上限, 合理库存下限, 组装商品, beactive, 应付金额, 已付金额, 应收金额, 已收金额, 分类编号, 商品规格) VALUES (N'" + textBoxSPBH.Text.Trim() + "', N'" + textBoxSPMC.Text.Trim() + "', N'" + textBoxZJM.Text.Trim() + "', N'" + textBoxZXJLDW.Text.Trim() + "', " + numericUpDownJJ.Value.ToString() + ", " + numericUpDownJJ.Value.ToString() + ", " + numericUpDownPFJ.Value.ToString() + ", " + numericUpDownPFJ.Value.ToString() + ", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '" + dateTimePickerDLRQ.Value.ToShortDateString() + "', " + numericUpDownKCSX.Value.ToString() + ", " + numericUpDownKCXX.Value.ToString() + ", " + numericUpDownHLSX.Value.ToString() + ", " + numericUpDownHLXX.Value.ToString() + ", 0, 1, 0, 0, 0, 0, " + iClass.ToString() + ", N'" + textBoxSPGG.Text.Trim() + "')";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "SELECT @@IDENTITY";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
iSelect = Convert.ToInt32(sqldr.GetValue(0).ToString());
sqldr.Close();
//增加库存
sqlComm.CommandText = "SELECT 库房ID FROM 商品分类表 WHERE (ID = " + iClass.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
string sKF = sqldr.GetValue(0).ToString();
sqldr.Close();
if (sKF != "")
{
sqlComm.CommandText = "INSERT INTO 库存表 (库房ID, 商品ID, 库存数量, 库存金额, 库存成本价, 核算成本价, 库存上限, 库存下限, 合理库存上限, 合理库存下限, 应付金额, 已付金额, 应收金额, 已收金额, BeActive) VALUES (" + sKF + ", " + iSelect.ToString() + ", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1)";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
MessageBox.Show("增加成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
break;
case 1: //修改
sqlConn.Open();
//查重
if (textBoxSPBH.Text.Trim() == "")
{
MessageBox.Show("请输入商品编号");
sqlConn.Close();
break;
}
iSelect = Convert.ToInt32(dt.Rows[0][0].ToString());
sqlComm.CommandText = "SELECT ID, 商品名称 FROM 商品表 WHERE (商品编号 = '" + textBoxSPBH.Text.Trim() + "' AND ID <> " + iSelect.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
MessageBox.Show("商品编号" + textBoxSPBH.Text.Trim() + "重复，名称为：" + sqldr.GetValue(1).ToString());
sqldr.Close();
sqlConn.Close();
break;
}
sqldr.Close();
sqlComm.CommandText = "SELECT ID, 商品编号 FROM 商品表 WHERE (商品名称 = '" + textBoxSPMC.Text.Trim() + "' AND ID <> " + iSelect.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
if (MessageBox.Show("商品名称" + textBoxSPMC.Text.Trim() + "重复，编号为：" + sqldr.GetValue(1).ToString() + "，是否继续？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.No)
{
sqldr.Close();
sqlConn.Close();
break;
}
}
sqldr.Close();
//使用状态
sqlComm.CommandText = "SELECT DISTINCT 商品表.商品名称 FROM 单据明细汇总视图 INNER JOIN 商品表 ON 单据明细汇总视图.商品ID = 商品表.ID WHERE (单据明细汇总视图.BeActive = 1) AND (单据明细汇总视图.商品ID = " + iSelect.ToString() + ")";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
if (textBoxSPMC.Text.Trim() != sqldr.GetValue(0).ToString())
{
MessageBox.Show("该商品已有单据保存，不可更改商品名称：" + sqldr.GetValue(0).ToString() + "。", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
textBoxSPMC.Text = sqldr.GetValue(0).ToString();
}
sqldr.Close();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//得到表单号
//得到服务器日期
sqlComm.CommandText = "SELECT GETDATE() AS 日期";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDateSYS = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
iSelect = Convert.ToInt32(dt.Rows[0][0].ToString());
sqlComm.CommandText = "UPDATE 商品表 SET 商品编号 = N'" + textBoxSPBH.Text.Trim() + "', 商品名称 = N'" + textBoxSPMC.Text.Trim() + "', 助记码 = N'" + textBoxZJM.Text.Trim() + "', 最小计量单位 = N'" + textBoxZXJLDW.Text.Trim() + "', 进价 = " + numericUpDownJJ.Value.ToString() + ", 含税进价 = " + numericUpDownJJ.Value.ToString() + ", 批发价 = " + numericUpDownPFJ.Value.ToString() + ", 含税批发价 = " + numericUpDownPFJ.Value.ToString() + ", 登录日期 = '" + dateTimePickerDLRQ.Value.ToShortDateString() + "', 库存上限 = " + numericUpDownKCSX.Value.ToString() + ", 库存下限 = " + numericUpDownKCXX.Value.ToString() + ", 合理库存上限 = " + numericUpDownHLSX.Value.ToString() + ", 合理库存下限 = " + numericUpDownHLXX.Value.ToString() + ", 分类编号 = " + iClass.ToString() + ", 商品规格 = N'" + textBoxSPGG.Text.Trim() + "' WHERE (ID = " + dt.Rows[0][0].ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
MessageBox.Show("修改成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
break;
default:
break;
}
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
int i, j;
//保存完毕
if (isSaved)
{
MessageBox.Show("库存盘点表已经保存,单据号为：" + labelDJBH.Text, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (alKF.Count == 0)
{
MessageBox.Show("请选择商品库房", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (dataGridViewDJMX.RowCount < 1)
{
MessageBox.Show("没有盘点商品", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MessageBox.Show("请检查库存盘点表内容,该制单内容不可更改，是否继续保存？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
{
return;
}
saveToolStripButton.Enabled = false;
string strCount = "", strDateSYS = "", strKey = "CPD";
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
//得到表单号
//得到服务器日期
sqlComm.CommandText = "SELECT GETDATE() AS 日期";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
strDateSYS = Convert.ToDateTime(sqldr.GetValue(0).ToString()).ToShortDateString();
}
sqldr.Close();
//得到日期
sqlComm.CommandText = "SELECT 时间 FROM 表单计数表 WHERE (时间 = CONVERT(DATETIME, '" + strDateSYS + " 00:00:00', 102))";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Close();
}
else //服务器时间不吻合
{
sqldr.Close();
//修正日期及计数器
sqlComm.CommandText = "UPDATE 表单计数表 SET 时间 = '" + strDateSYS + "', 计数 = 1";
sqlComm.ExecuteNonQuery();
}
//得到计数器
sqlComm.CommandText = "SELECT 计数 FROM 表单计数表 WHERE (关键词 = N'" + strKey + "')";
sqldr = sqlComm.ExecuteReader();
if (sqldr.HasRows)
{
sqldr.Read();
strCount = sqldr.GetValue(0).ToString();
sqldr.Close();
//增加计数器
sqlComm.CommandText = "UPDATE 表单计数表 SET 计数 = 计数 + 1 WHERE (关键词 = N'" + strKey + "')";
sqlComm.ExecuteNonQuery();
}
else
{
sqldr.Close();
}
if (strCount != "")
{
strCount = string.Format("{0:D3}", Int32.Parse(strCount));
strCount = strKey.ToUpper() + Convert.ToDateTime(strDateSYS).ToString("yyyyMMdd") + strCount;
}
else
{
MessageBox.Show("数据错误", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
sqlConn.Close();
return;
}
//表单汇总
sqlComm.CommandText = "INSERT INTO 库存盘点汇总表(单据编号, 日期, 业务员ID, 操作员ID, 盘点标记, 备注, 库房ID, 商品ID, 分类ID, 数量合计, 金额合计, 盘损数量合计, 盘损金额合计, BeActive) VALUES (N'" + strCount + "', '" + strDateSYS + "', " + comboBoxYWY.SelectedValue.ToString() + ", " + intUserID.ToString() + ", 0, N'" + textBoxBZ.Text.Trim() + "', " + intKFID.ToString() + ", " + intCommID.ToString() + ", " + intClassID.ToString() + ", " + labelSLHJ.Text + ", " + labelJEHJ.Text + ", 0, 0, 1)";
sqlComm.ExecuteNonQuery();
//取得单据号
sqlComm.CommandText = "SELECT @@IDENTITY";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
string sBillNo = sqldr.GetValue(0).ToString();
sqldr.Close();
//单据明细
for (i = 0; i < dataGridViewDJMX.Rows.Count; i++)
{
if (dataGridViewDJMX.Rows[i].IsNewRow)
{
continue;
}
sqlComm.CommandText = "INSERT INTO 库存盘点明细表 (单据ID, 商品ID, 结存数量, 结存金额, 实盘数量, 备注, 盘点标志,库房ID) VALUES (" + sBillNo + ", " + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[3].Value.ToString() + ", " + dataGridViewDJMX.Rows[i].Cells[4].Value.ToString() + ", 0, N'" + dataGridViewDJMX.Rows[i].Cells[5].Value.ToString() + "', 0, " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
saveToolStripButton.Enabled = true;
return;
}
finally
{
sqlConn.Close();
}
//MessageBox.Show("库存盘点表保存成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
labelDJBH.Text = strCount;
this.Text = "库存盘点表：" + labelDJBH.Text;
isSaved = true;
if (MessageBox.Show("库存盘点表保存成功，是否继续开始另一份单据？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
MDIBusiness mdiT = (MDIBusiness)this.MdiParent;
mdiT.准备盘点表AToolStripMenuItem_Click(null, null);
}
if (MessageBox.Show("是否关闭制单窗口", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
this.Close();
}
}
private void TestSignals(string Tiker)
{
string sdelka = "";
int SignalsCount = 0;
int LossCount = 0;
int ProfitCount = 0;
int PaperCount = 0;
int StepCount = 0;
int StepIncrement = 0;
double OpenPrice = 0;
double Deposit = 35000;
double StopLoss = 0;
double TakeProfit = 0;
double Step = 0;
bool inStockDown = false;
bool inStockUp = false;
var ID = (from c in db.Table where c.Tiker == Tiker orderby c.Id ascending select c.Id).ToArray();
var H = (from c in db.Table where c.Tiker == Tiker orderby c.Id ascending select c.High).ToArray();
var L = (from c in db.Table where c.Tiker == Tiker orderby c.Id ascending select c.Low).ToArray();
var O = (from c in db.Table where c.Tiker == Tiker orderby c.Id ascending select c.Open).ToArray();
var BPU = (from c in db.Table where c.Tiker == Tiker orderby c.Id ascending select c.BPU).ToArray();
for (int i = 2; i < ID.Count() - 2; i++)
{
// if(i== ID.Count()-1)
// MessageBox.Show(ID.GetValue(i).ToString());
if (Convert.ToString(BPU.GetValue(i)).TrimEnd() == "down" && inStockDown == false && inStockUp == false)
{
StopLoss = Convert.ToDouble(H.GetValue(i)) * (1 + Convert.ToDouble(textBox1.Text) / 100);
i = i + 2;
inStockDown = true;
OpenPrice = Convert.ToDouble(O.GetValue(i));
/* if (Convert.ToDouble((StopLoss - OpenPrice) * 100 / StopLoss) > Convert.ToDouble(textBox3.Text))
* { inStockDown = false; }*/
TakeProfit = Convert.ToDouble(O.GetValue(i)) * (1 - Convert.ToDouble(textBox2.Text) / 100);
PaperCount = 1; //(Convert.ToInt32(Deposit / OpenPrice) - 1)*7;
Step = (OpenPrice - TakeProfit) / Convert.ToInt32(textBox4.Text); // Дельту профита разделили на кол-во частей
StepCount = Convert.ToInt32(PaperCount / Convert.ToInt32(textBox4.Text)); // кол-во бумаг разделили на кол-во степов - 1
if (StepCount * Convert.ToInt32(textBox4.Text) > PaperCount)
{
StepCount = StepCount - 1;
}
StepIncrement = 1;
if (inStockDown == true)
{
sdelka = Convert.ToString(ID.GetValue(i)) + " Продажа. Цена " + OpenPrice.ToString() + " Стоп: " + StopLoss.ToString() + " Профит: " + TakeProfit.ToString() + " Бумаг всего: " + PaperCount.ToString() + " Степ: " + StepCount.ToString();;
SignalsCount = SignalsCount + 1;
// listBox3.Items.Add(sdelka);
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE [Table] SET Trade = 'Sell' WHERE id='" + ID.GetValue(i - 1).ToString() + "';" + "UPDATE [Table] SET TradeComment = N'" + sdelka + "' WHERE id='" + ID.GetValue(i - 1).ToString() + "';" + "UPDATE [Table] SET Capital = " + Deposit.ToString().Replace(",", ".") + " WHERE id='" + ID.GetValue(i - 1).ToString() + "';";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
}
if (Convert.ToString(BPU.GetValue(i)).TrimEnd() == "up" && inStockDown == false && inStockUp == false)
{
StopLoss = Convert.ToDouble(L.GetValue(i)) * (1 - Convert.ToDouble(textBox1.Text) / 100);
i = i + 2;
inStockUp = true;
OpenPrice = Convert.ToDouble(O.GetValue(i));
if (Convert.ToDouble((OpenPrice - StopLoss) * 100 / OpenPrice) > Convert.ToDouble(textBox3.Text))
{
inStockUp = false;
}
TakeProfit = Convert.ToDouble(O.GetValue(i)) * (1 + Convert.ToDouble(textBox2.Text) / 100);
PaperCount = 1; // ( Convert.ToInt32(Deposit / OpenPrice) - 1)*7;
Step = (TakeProfit - OpenPrice) / Convert.ToInt32(textBox4.Text); // Дельту профита разделили на кол-во частей
StepCount = Convert.ToInt32(PaperCount / Convert.ToInt32(textBox4.Text)); // кол-во бумаг разделили на кол-во степов - 1
if (StepCount * Convert.ToInt32(textBox4.Text) > PaperCount)
{
StepCount = StepCount - 1;
}
StepIncrement = 1;
if (inStockUp == true)
{
sdelka = Convert.ToString(ID.GetValue(i)) + " Покупка. Цена " + OpenPrice.ToString() + " Стоп: " + StopLoss.ToString() + " Профит: " + TakeProfit.ToString() + " Бумаг всего: " + PaperCount.ToString() + " Степ: " + StepCount.ToString();
// listBox3.Items.Add(sdelka);
SignalsCount = SignalsCount + 1;
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE [Table] SET Trade = 'Buy' WHERE id='" + ID.GetValue(i - 1).ToString() + "';" + "UPDATE [Table] SET TradeComment = N'" + sdelka + "' WHERE id='" + ID.GetValue(i - 1).ToString() + "';" + "UPDATE [Table] SET Capital = " + Deposit.ToString().Replace(",", ".") + " WHERE id='" + ID.GetValue(i - 1).ToString() + "';";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
}
if (inStockUp == true && StepIncrement < Convert.ToInt32(textBox4.Text) && checkBox2.Checked == true && Convert.ToDouble(H.GetValue(i)) > (OpenPrice + Step * StepIncrement))
{
Deposit = Deposit + ((OpenPrice + Step * StepIncrement) - OpenPrice) * StepCount - OpenPrice * StepCount * 0.00046 - (OpenPrice + Step * StepIncrement) * StepCount * 0.00046;
PaperCount = PaperCount - StepCount;
StepIncrement++;
// listBox3.Items.Add("Частичный выход по профиту. " + "Депозит: " + Deposit.ToString() + " Остаток бумаг1: " + PaperCount.ToString());
}
if (inStockUp == true && Convert.ToDouble(H.GetValue(i)) > TakeProfit)
{
ProfitCount = ProfitCount + 1;
Deposit = Deposit + (TakeProfit - OpenPrice) * PaperCount - PaperCount * 6;
inStockUp = false;
// listBox3.Items.Add("Выход по профиту. " + "Депозит: " + Deposit.ToString() + "Остаток бумаг:" + PaperCount.ToString());
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE [Table] SET Trade = 'Profit' WHERE id='" + ID.GetValue(i).ToString() + "';" + "UPDATE [Table] SET Capital = " + Deposit.ToString().Replace(",", ".") + " WHERE id='" + ID.GetValue(i).ToString() + "';";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
if (inStockDown == true && StepIncrement < Convert.ToInt32(textBox4.Text) && checkBox2.Checked == true && Convert.ToDouble(L.GetValue(i)) < (OpenPrice - Step * StepIncrement))
{
Deposit = Deposit + (OpenPrice - (OpenPrice - Step * StepIncrement)) * StepCount - OpenPrice * StepCount * 0.00046 - (OpenPrice - Step * StepIncrement) * StepCount * 0.00046;
PaperCount = PaperCount - StepCount;
StepIncrement++;
// listBox3.Items.Add("Частичный выход по профиту. " + "Депозит: " + Deposit.ToString() + " Остаток бумаг:" + PaperCount.ToString());
}
if (inStockDown == true && Convert.ToDouble(L.GetValue(i)) < TakeProfit)
{
ProfitCount = ProfitCount + 1;
Deposit = Deposit + (OpenPrice - TakeProfit) * PaperCount - PaperCount * 6;
inStockDown = false;
// listBox3.Items.Add("Выход по профиту. " + "Депозит:" + Deposit.ToString() + " Остаток бумаг:" + PaperCount.ToString());
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE [Table] SET Trade = 'Profit' WHERE id='" + ID.GetValue(i).ToString() + "';" + "UPDATE [Table] SET Capital = " + Deposit.ToString().Replace(",", ".") + " WHERE id='" + ID.GetValue(i).ToString() + "';";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
if (inStockUp == true && Convert.ToDouble(L.GetValue(i)) < StopLoss)
{
if (ID.GetValue(i).ToString().Substring(8, 6) == "100000")
{
StopLoss = Convert.ToDouble(L.GetValue(i));
}
LossCount = LossCount + 1;
Deposit = Deposit - (OpenPrice - StopLoss) * PaperCount - PaperCount * 6;
inStockUp = false;
// listBox3.Items.Add("Выход по стоплосс. " + "Депозит:" + Deposit.ToString() + " Остаток бумаг:" + PaperCount.ToString());
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE [Table] SET Trade = 'StopLoss' WHERE id='" + ID.GetValue(i).ToString() + "';" + "UPDATE [Table] SET Capital = " + Deposit.ToString().Replace(",", ".") + " WHERE id='" + ID.GetValue(i).ToString() + "';";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
if (inStockDown == true && Convert.ToDouble(H.GetValue(i)) > StopLoss)
{
if (ID.GetValue(i).ToString().Substring(8, 6) == "100000")
{
StopLoss = Convert.ToDouble(H.GetValue(i));
}
LossCount = LossCount + 1;
Deposit = Deposit - (StopLoss - OpenPrice) * PaperCount - PaperCount * 6;
inStockDown = false;
// listBox3.Items.Add("Выход по стоплосс. " + "Депозит:" + Deposit.ToString() + " Остаток бумаг:" + PaperCount.ToString());
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE [Table] SET Trade = 'StopLoss' WHERE id='" + ID.GetValue(i).ToString() + "';" + "UPDATE [Table] SET Capital = " + Deposit.ToString().Replace(",", ".") + " WHERE id='" + ID.GetValue(i).ToString() + "';";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
}
label16.Text = SignalsCount.ToString() + " " + Deposit.ToString();
/*listBox2.Items.Add("Выиграных: " + ProfitCount.ToString());
* listBox2.Items.Add("Проиграных: " + LossCount.ToString());
* listBox2.Items.Add("Депоз*/
}
private void toolStripButtonDEL_Click(object sender, EventArgs e)
{
if (dataGridViewProduct.SelectedRows.Count < 1)
{
MessageBox.Show("please select products", "Infomation", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (MessageBox.Show("do you want to delete product？", "Information", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
{
return;
}
int i;
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
for (i = 0; i < dataGridViewProduct.SelectedRows.Count; i++)
{
if (dataGridViewProduct.Rows[i].IsNewRow)
{
continue;
}
sqlComm.CommandText = "DELETE FROM product WHERE (ID = " + dataGridViewProduct.SelectedRows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "DELETE FROM acquire WHERE ([Product ID] = " + dataGridViewProduct.SelectedRows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "DELETE FROM actual WHERE ([Product ID] = " + dataGridViewProduct.SelectedRows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "DELETE FROM buyer WHERE ([Product ID] = " + dataGridViewProduct.SelectedRows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "DELETE FROM orders WHERE ([Product ID] = " + dataGridViewProduct.SelectedRows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "DELETE FROM TAC WHERE ([Product ID] = " + dataGridViewProduct.SelectedRows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("error：" + ex.Message.ToString(), "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
MessageBox.Show("delete finished", "infomation", MessageBoxButtons.OK, MessageBoxIcon.Information);
initDataView(0);
}
} // MapDataFields (System.Data.DataRow currentRow)
public override Boolean Save()
{
Boolean success = false;
StringBuilder sqlStatement = new StringBuilder();
Boolean usingTransaction = true;
if (Id != 0)
{
return(true);
} // CANNOT MODIFIY EXISTING NOTE CONTENTS
ModifiedAccountInfo = new Mercury.Server.Data.AuthorityAccountStamp(application);
try {
if (application.EnvironmentDatabase.OpenTransactions == 0)
{
usingTransaction = true;
base.application.EnvironmentDatabase.BeginTransaction();
}
System.Data.SqlClient.SqlCommand insertCommand = application.EnvironmentDatabase.CreateSqlCommand("dal.EntityNoteContent_Insert");
insertCommand.CommandType = System.Data.CommandType.StoredProcedure;
insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@entityNoteId", System.Data.SqlDbType.BigInt));
insertCommand.Parameters["@entityNoteId"].Value = entityNoteId;
insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@content", System.Data.SqlDbType.Text));
insertCommand.Parameters["@content"].Value = content;
insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@modifiedAuthorityName", System.Data.SqlDbType.VarChar, 60));
insertCommand.Parameters["@modifiedAuthorityName"].Value = modifiedAccountInfo.SecurityAuthorityName;
insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@modifiedAccountId", System.Data.SqlDbType.VarChar, 60));
insertCommand.Parameters["@modifiedAccountId"].Value = modifiedAccountInfo.UserAccountId;
insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@modifiedAccountName", System.Data.SqlDbType.VarChar, 60));
insertCommand.Parameters["@modifiedAccountName"].Value = modifiedAccountInfo.UserAccountName;
if (insertCommand.ExecuteNonQuery() != 1)
{
base.application.SetLastException(base.application.EnvironmentDatabase.LastException);
throw base.application.EnvironmentDatabase.LastException;
}
if (id == 0) // RESET DOCUMENT ID CRITERIA
{
Object identity = base.application.EnvironmentDatabase.ExecuteScalar("SELECT @@IDENTITY").ToString();
if (!Int64.TryParse((String)identity, out id))
{
throw new ApplicationException("Unable to retreive unique id.");
}
}
success = true;
if (usingTransaction)
{
base.application.EnvironmentDatabase.CommitTransaction();
}
}
catch (Exception applicationException) {
success = false;
if (usingTransaction)
{
base.application.EnvironmentDatabase.RollbackTransaction();
}
base.application.SetLastException(applicationException);
}
return(success);
}
private void btnAdd_Click(object sender, EventArgs e)
{
Boolean isOk = true;
Int32 multiCoatCount = 1;
if (MessageBox.Show("Add this Job to the current Batch ?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
System.Data.SqlClient.SqlTransaction trnEnvelope = VPSConnection.BeginTransaction();
if (movedFromOtherLine == true)
{
}
else
{
myJOBData.ProgressCoats = myJOBData.PaintSystemCoats;
//if (myJOBData.PaintSystemProcessCode == "1")
// myJOBData.ProgressCoats = 2;
//else if (myJOBData.PaintSystemProcessCode == "2")
// myJOBData.ProgressCoats = 3;
//else if (myJOBData.PaintSystemProcessCode == "3")
// myJOBData.ProgressCoats = 4;
//else if (myJOBData.PaintSystemProcessCode == "4")
// myJOBData.ProgressCoats = 5;
if (myJOBData.ProgressCoats > 1)
{
multiCoatCount = myJOBData.Get_MultiCoat_Progress_Records(myJOBData.JobId, trnEnvelope) + 1;
}
for (int i = multiCoatCount; i <= myJOBData.ProgressCoats; i++)
{
myJOBData.ProgressThisCoat = i;
if (myJOBData.Insert_Progress_Record(myJOBData.JobId, productionLineId, trnEnvelope) == true)
{
if (cmbInsert.Text != cmbInsert.Items[0].ToString())
{
// Job to be inserted in the list but not at the end
String[] followingJob = cmbInsert.Text.Split('#');
try
{
// Get Job in front of which this Job is to be inserted
DataTable myFollower = new DataTable();
String strSQL = "SELECT * FROM Jobs WHERE JobNumber = '" + followingJob[1].Trim() + "'";
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, VPSConnection, trnEnvelope);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
myFollower.Load(rdrGet);
}
else
{
isOk = false;
break;
}
rdrGet.Close();
cmdGet.Dispose();
// Update this Job with Schedule Date & Sequence
if (isOk == true)
{
if (myFollower.Rows.Count > 0)
{
strSQL = "UPDATE Jobs SET ScheduleDate = CONVERT(datetime, '" + Convert.ToDateTime(myFollower.Rows[0]["ScheduleDate"]).ToString() + "', 103), ";
strSQL = strSQL + "ScheduleSeq = " + (Convert.ToInt32(myFollower.Rows[0]["ScheduleSeq"]) - 1).ToString() + " ";
strSQL = strSQL + "WHERE JobId = " + myJOBData.JobId.ToString();
System.Data.SqlClient.SqlCommand cmdUpdate = new System.Data.SqlClient.SqlCommand(strSQL, VPSConnection, trnEnvelope);
cmdUpdate.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
isOk = false;
break;
}
}
}
else
{
isOk = false;
break;
}
}
if (isOk == true)
{
trnEnvelope.Commit();
this.DialogResult = DialogResult.Yes;
this.Hide();
}
else
{
trnEnvelope.Rollback();
MessageBox.Show(myJOBData.ErrorMessage, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
txtJobNumber.Text = string.Empty;
txtJobNumber.Focus();
}
}
private void btnYes_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlTransaction sqlta;
int i;
if (textBoxCname.Text.Trim() == "")
{
MessageBox.Show("会议名称不能为空！", "输入错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
sqlConn.Open();
sqlComm.Connection = sqlConn;
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
switch (intMode)
{
case 1: //增加会议
try
{
sqlComm.CommandText = "INSERT INTO 培训表 (会议主题, 会议副题, 会议内容, 会场号, 开始时间, 结束时间) VALUES (N'" + textBoxCname.Text.Trim() + "', N'" + textBoxCname2.Text.Trim() + "', N'" + textBoxCintro.Text.Trim() + "', N'" + textBoxConferenceNo.Text.Trim() + "', '" + dateTimePickerStart.Value.ToString() + "', '" + dateTimePickerEnd.Value.ToString() + "')";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "SELECT @@IDENTITY";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
string strCId = sqldr.GetValue(0).ToString();
sqldr.Close();
//参会人员表
btnsAll_Click(null, null);
foreach (DataGridViewRow r in dataGridViewPeople.Rows)
{
if ((bool)r.Cells[0].Value && (int)r.Cells[3].Value > 0)
{
sqlComm.CommandText = "INSERT INTO 培训人员表 (会议ID, 参会单位, 参会人数, 到会人数) VALUES (" + strCId + ", N'" + r.Cells[2].Value.ToString().Trim() + "', " + r.Cells[3].Value.ToString() + ", 0)";
sqlComm.ExecuteNonQuery();
}
}
sqlta.Commit();
}
catch (Exception err)
{
//回滚
MessageBox.Show("数据库错误！", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
sqlConn.Close();
return;
}
finally
{
MessageBox.Show("会议已加入到数据库！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
sqlConn.Close();
break;
case 2: //修改会议
try
{
sqlComm.CommandText = "UPDATE 培训表 SET 会议主题 = N'" + textBoxCname.Text.Trim() + "', 会议副题 = N'" + textBoxCname2.Text.Trim() + "', 会议内容 = N'" + textBoxCintro.Text.Trim() + "', 会场号 = N'" + textBoxConferenceNo.Text.Trim() + "', 开始时间 = '" + dateTimePickerStart.Value.ToString() + "', 结束时间 = '" + dateTimePickerEnd.Value.ToString() + "' WHERE (会议ID = " + intConferenceID.ToString() + ")";
sqlComm.ExecuteNonQuery();
//参会人员表
sqlComm.CommandText = "DELETE FROM 培训签到表 WHERE (会议ID = " + intConferenceID.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "DELETE FROM 培训新到人员表 WHERE (会议ID = " + intConferenceID.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "DELETE FROM 培训人员表 WHERE (会议ID = " + intConferenceID.ToString() + ")";
sqlComm.ExecuteNonQuery();
btnsAll_Click(null, null);
foreach (DataGridViewRow r in dataGridViewPeople.Rows)
{
if ((bool)r.Cells[0].Value && (int)r.Cells[3].Value > 0)
{
sqlComm.CommandText = "INSERT INTO 培训人员表 (会议ID, 参会单位, 参会人数, 到会人数) VALUES (" + intConferenceID.ToString() + ", N'" + r.Cells[2].Value.ToString().Trim() + "', " + r.Cells[3].Value.ToString() + ", 0)";
sqlComm.ExecuteNonQuery();
}
}
sqlta.Commit();
}
catch (Exception err)
{
//回滚
MessageBox.Show("数据库错误！", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
sqlConn.Close();
return;
}
finally
{
MessageBox.Show("会议信息已修改！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
sqlConn.Close();
break;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string From = ddlFrom.Text;
string To = ddlTo.Text;
GridView1.Visible = true;
GridView2.Visible = true;
DateTime todate = DateTime.Parse(tbDepartureDate.Text);
DateTime fromdate = DateTime.Parse(tbReturnDate.Text);
try
{
if (From == To)
{
lblError.Visible = true;
lblError.Text = "Please select two different cities to travel between.";
}
else
{
lblError.Visible = false;
}
if (todate > fromdate)
{
lblCalError.Visible = true;
lblCalError.Text = "Please select a return date that is after your departure date.";
}
else if (todate.Equals("1/1/0001 12:00:00 AM"))
{
lblCalError.Visible = true;
lblCalError.Text = "Please select a departure date.";
}
else if (fromdate.Equals("1/1/0001 12:00:00 AM"))
{
lblCalError.Visible = true;
lblCalError.Text = "Please select a return date.";
}
else
{
lblError.Visible = false;
lblCalError.Visible = false;
lblError.Text = "";
lblCalError.Text = "";
System.Data.SqlClient.SqlConnection sc1 = new System.Data.SqlClient.SqlConnection();
sc1.ConnectionString = @"Server = Localhost; Database=KPMG; Trusted_Connection=Yes";
sc1.Open();
System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand();
cmd1.Connection = sc1;
cmd1.Parameters.AddWithValue("@FromCode", From);
cmd1.Parameters.AddWithValue("@ToCode", To);
//SQL statement to check for max modified date for verification
cmd1.CommandText = "select * from dbo.Train_Schedule where [From_Station_City] = @FromCode and To_Station_City = @ToCode";
cmd1.ExecuteNonQuery();
System.Data.SqlClient.SqlDataReader reader1;
reader1 = cmd1.ExecuteReader();
reader1.Read();
DataTable dt = new DataTable();
dt.Columns.Add("Select");
dt.Columns.Add("Train Number");
dt.Columns.Add("From Code");
dt.Columns.Add("From");
dt.Columns.Add("To Code");
dt.Columns.Add("To");
dt.Columns.Add("Dep Time");
dt.Columns.Add("Arrival Time");
dt.Columns.Add("Price");
DataRow dr = dt.NewRow();
int i = 1;
int z = 0;
while (reader1.Read())
{
// RadioButton radioButton1 = new RadioButton();
// radioButton1.GroupName = "Departure_Buttons";
// radioButton1.Checked += RadioButton_Checked;
dt.Rows.Add(i.ToString(), reader1[0].ToString(), reader1[1].ToString(), reader1[2].ToString(), reader1[3].ToString(), reader1[4].ToString(), reader1[5].ToString(), reader1[6].ToString(), reader1[7].ToString());
Train[] traindata = new Train[reader1.FieldCount];
traindata[z] = new Train(i, reader1[0].ToString(), reader1[1].ToString(), reader1[2].ToString(), reader1[3].ToString(), reader1[4].ToString(), reader1[5].ToString(), reader1[6].ToString(), reader1[7].ToString());
i = i + 1;
z = z + 1;
}
GridView1.DataSource = dt;
GridView1.DataBind();
System.Data.SqlClient.SqlCommand cmdCount = new System.Data.SqlClient.SqlCommand();
cmdCount.Connection = sc1;
cmdCount.Parameters.AddWithValue("@FromCode", From);
cmdCount.Parameters.AddWithValue("@ToCode", To);
// cmdCount.CommandText = "select Row_Number() over (order by From_Station_City) as Row_Number from dbo.Train_Schedule where [From_Station_City] = @FromCode and To_Station_City = @ToCode";
// cmdCount.ExecuteNonQuery();
// ddlNumb.DataSource = cmdCount;
sc1.Close();
Label12.Visible = true;
Label13.Visible = true;
lblReturn.Text = fromdate.ToString("M/dd/yyyy");
lblDepart.Text = todate.ToString("M/dd/yyyy");
//SQL to get the return table
System.Data.SqlClient.SqlConnection sc2 = new System.Data.SqlClient.SqlConnection();
sc2.ConnectionString = @"Server = Localhost; Database=KPMG; Trusted_Connection=Yes";
sc2.Open();
System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand();
cmd2.Connection = sc2;
cmd2.Parameters.AddWithValue("@FromCode", From);
cmd2.Parameters.AddWithValue("@ToCode", To);
cmd2.CommandText = "select * from dbo.Train_Schedule where [From_Station_City] = @ToCode and To_Station_City = @FromCode";
cmd2.ExecuteNonQuery();
System.Data.SqlClient.SqlDataReader reader2;
reader2 = cmd2.ExecuteReader();
reader2.Read();
DataTable dt2 = new DataTable();
dt2.Columns.Add("Select");
dt2.Columns.Add("Train Number");
dt2.Columns.Add("From Code");
dt2.Columns.Add("From");
dt2.Columns.Add("To Code");
dt2.Columns.Add("To");
dt2.Columns.Add("Dep Time");
dt2.Columns.Add("Arrival Time");
dt2.Columns.Add("Price");
DataRow dr2 = dt.NewRow();
i = 1;
z = 0;
while (reader2.Read())
{
dt2.Rows.Add(i.ToString(), reader2[0].ToString(), reader2[1].ToString(), reader2[2].ToString(), reader2[3].ToString(), reader2[4].ToString(), reader2[5].ToString(), reader2[6].ToString(), reader2[7].ToString());
Train[] traindata2 = new Train[reader2.FieldCount];
traindata2[z] = new Train(i, reader2[0].ToString(), reader2[1].ToString(), reader2[2].ToString(), reader2[3].ToString(), reader2[4].ToString(), reader2[5].ToString(), reader2[6].ToString(), reader2[7].ToString());
i++;
z++;
}
GridView2.DataSource = dt2;
GridView2.DataBind();
}
}
catch
{
lblError.Text = "We Caught an error!";
}
}
private void button1_Click(object sender, EventArgs e)
{
int i, j;
string st, stt, stt1;
string st1, st2;
sqlConn.Open();
sqlComm.CommandText = "UPDATE 商品表 SET 应付金额 = 0, 已付金额 = 0, 应收金额 = 0, 已收金额 = 0, 库存数量 = 0";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 库存表 SET 库存数量 = 0, 应付金额 = 0, 已付金额 = 0, 应收金额 = 0, 已收金额 = 0";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "SELECT 商品表.ID, bbb.name, bbb.num, bbb.cb FROM bbb INNER JOIN 商品表 ON bbb.name = 商品表.商品名称";
if (dSet.Tables.Contains("temp"))
{
dSet.Tables.Remove("temp");
}
sqlDA.Fill(dSet, "temp");
for (i = 0; i < dSet.Tables["temp"].Rows.Count; i++)
{
sqlComm.CommandText = "UPDATE 商品表 SET 库存数量 = " + dSet.Tables["temp"].Rows[i][2].ToString() + ", 库存成本价= " + dSet.Tables["temp"].Rows[i][3].ToString() + " WHERE (ID = " + dSet.Tables["temp"].Rows[i][0].ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 库存表 SET 库存数量 = " + dSet.Tables["temp"].Rows[i][2].ToString() + ", 库存成本价= " + dSet.Tables["temp"].Rows[i][3].ToString() + " WHERE (商品ID = " + dSet.Tables["temp"].Rows[i][0].ToString() + ")";
sqlComm.ExecuteNonQuery();
}
sqlComm.CommandText = "UPDATE 商品表 SET 库存金额 = 库存数量 * 库存成本价";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 库存表 SET 库存金额 = 库存数量 * 库存成本价";
sqlComm.ExecuteNonQuery();
MessageBox.Show("完了");
sqlConn.Close();
}
private void btnAccept_Click(object sender, EventArgs e)
{
int i;
System.Data.SqlClient.SqlTransaction sqlta;
if (!countAmount())
{
MessageBox.Show("输入类型错误", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
switch (iStyle)
{
case 0: //增加
sqlConn.Open();
//查重
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
for (i = 0; i < dataGridViewDA.Rows.Count; i++)
{
if (dataGridViewDA.Rows[i].IsNewRow)
{
continue;
}
sqlComm.CommandText = "INSERT INTO 部门表 (部门编号, 部门名称, 助记码, BeActive) VALUES (N'" + dataGridViewDA.Rows[i].Cells[1].Value.ToString() + "', N'" + dataGridViewDA.Rows[i].Cells[2].Value.ToString() + "', '" + dataGridViewDA.Rows[i].Cells[3].Value.ToString() + "', 1)";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
MessageBox.Show("增加成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
break;
case 1: //修改
sqlConn.Open();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
for (i = 0; i < dataGridViewDA.Rows.Count; i++)
{
if (dataGridViewDA.Rows[i].IsNewRow)
{
continue;
}
sqlComm.CommandText = "UPDATE 部门表 SET 部门编号 = N'" + dataGridViewDA.Rows[i].Cells[1].Value.ToString() + "', 部门名称 = N'" + dataGridViewDA.Rows[i].Cells[2].Value.ToString() + "', 助记码 = '" + dataGridViewDA.Rows[i].Cells[3].Value.ToString() + "' WHERE (ID = " + dataGridViewDA.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
}
sqlta.Commit();
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
MessageBox.Show("修改成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
break;
default:
break;
}
}
/// <summary>
/// Method for logging, calling this method will store the message on the specific target
/// </summary>
/// <param name="logType">Specify the type of the message (Message, Warining or Error)</param>
/// <param name="target">Specify where the record will be stored</param>
/// <param name="message">Specify the message of the log.</param>
public static void LogMessage(LogType logType, LogTarget target, string message)
{
if (string.IsNullOrEmpty(message))
{
return;
}
message = message.Trim();
if (target.HasFlag(LogTarget.Console))
{
switch (logType)
{
case LogType.Message:
Console.ForegroundColor = ConsoleColor.White;
break;
case LogType.Error:
Console.ForegroundColor = ConsoleColor.Red;
break;
case LogType.Warning:
Console.ForegroundColor = ConsoleColor.Yellow;
break;
default:
Console.ForegroundColor = ConsoleColor.White;
break;
}
Console.WriteLine(string.Format("{0} {1}", DateTime.Today.ToString("yyyyMMdd"), message));
}
if (target.HasFlag(LogTarget.File))
{
try
{
string path = System.IO.Path.Combine(System.Configuration.ConfigurationManager.AppSettings["Log_FileDirectory"], string.Concat(CONST_LogFile_Prefix, DateTime.Today.ToString("yyyyMMdd"), ".txt"));
string content = string.Empty;
if (System.IO.File.Exists(path))
{
content = System.IO.File.ReadAllText(path);
}
StringBuilder sBuilder = new StringBuilder(content);
sBuilder.AppendLine(string.Format("{0} {1}", DateTime.Today.ToString("yyyyMMdd"), message));
System.IO.File.WriteAllText(path, sBuilder.ToString());
}
catch (System.IO.IOException ioExc)
{
throw ioExc;
}
}
if (target.HasFlag(LogTarget.Database))
{
System.Data.SqlClient.SqlConnection connection = null;
using (connection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
try
{
System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
command.CommandText = "exec InsertintoLogValues '" + message + "'," + Convert.ToString(logType) + "";
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException sqlExc)
{
throw sqlExc;
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
}
}
}
public void StoreToDatabase()
{
using (System.Data.SqlClient.SqlConnection connection = ReportProvider.DatabaseConnection)
{
connection.Open();
System.Data.SqlClient.SqlCommand command;
//If the report has a Guid which is not Empty then it has been serialized before
if (ReportId != Guid.Empty)
{
//update
//should also remove old entities from the data set before serializing
command = new System.Data.SqlClient.SqlCommand("UPDATE ReportStore SET reportBinary = @reportBinary, friendlyName = @friendlyName", connection);
command.Parameters.AddWithValue("reportBinary", Serialize());
command.Parameters.AddWithValue("friendlyName", FriendlyName);
int affected = command.ExecuteNonQuery();
command.Dispose();
command = null;
command = new System.Data.SqlClient.SqlCommand("INSERT INTO ReportMap (reportId, entityId) Values (@reportId, @entityId)", connection);
command.Parameters.AddWithValue("reportId", reportId);
command.Parameters.AddWithValue("entityId", null);
//foreach Entity add the supporting entry to the ReportMap
foreach (object entity in ReportEntities)
{
try
{
command.Parameters["entityId"].Value = entity;
affected = command.ExecuteNonQuery();
}
catch
{
//already there see above notes...
}
}
}
else
{
//insert
command = new System.Data.SqlClient.SqlCommand("INSERT INTO ReportStore (reportId, reportType, reportBinary, startDate, endDate, friendlyName) Values (@reportId, @reportType, @reportBinary, @startDate, @endDate, @friendlyName)", connection);
reportId = Guid.NewGuid();
command.Parameters.AddWithValue("reportId", reportId);
command.Parameters.AddWithValue("reportType", ReportTypeString);
command.Parameters.AddWithValue("reportBinary", Serialize());
command.Parameters.AddWithValue("startDate", StartDate);
command.Parameters.AddWithValue("endDate", EndDate);
command.Parameters.AddWithValue("friendlyName", FriendlyName);
int affected = command.ExecuteNonQuery();
command.Dispose();
command = null;
command = new System.Data.SqlClient.SqlCommand("INSERT INTO ReportMap (reportId, entityId) Values (@reportId, @entityId)", connection);
command.Parameters.AddWithValue("reportId", reportId);
command.Parameters.AddWithValue("entityId", null);
//foreach Entity add the supporting entry to the ReportMap
foreach (object entity in ReportEntities)
{
command.Parameters["entityId"].Value = entity;
affected = command.ExecuteNonQuery();
}
}
command.Dispose();
command = null;
}
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
int i, j;
decimal dKUL = 0;
decimal dKUL1 = 0;
decimal fTemp = 0, fTemp1 = 0;
//保存完毕
if (toolStripStatusLabelMXJLS.Text == "0")
{
MessageBox.Show("没有商品库存定义", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!countAmount())
{
MessageBox.Show("商品库存上下限定义错误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
System.Data.SqlClient.SqlTransaction sqlta;
sqlConn.Open();
sqlta = sqlConn.BeginTransaction();
sqlComm.Transaction = sqlta;
try
{
for (i = 0; i < dataGridViewDJMX.Rows.Count; i++)
{
if (dataGridViewDJMX.Rows[i].IsNewRow)
{
continue;
}
if (intKFID == 0)
{
sqlComm.CommandText = "UPDATE 商品表 SET 库存下限 = " + dataGridViewDJMX.Rows[i].Cells[4].Value.ToString() + ", 合理库存下限 = " + dataGridViewDJMX.Rows[i].Cells[5].Value.ToString() + ", 合理库存上限 = " + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + ", 库存上限 = " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + " WHERE (ID = " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
sqlComm.CommandText = "UPDATE 库存表 SET 库存下限 = " + dataGridViewDJMX.Rows[i].Cells[4].Value.ToString() + ", 合理库存下限 = " + dataGridViewDJMX.Rows[i].Cells[5].Value.ToString() + ", 合理库存上限 = " + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + ", 库存上限 = " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + " WHERE (商品ID = " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
}
else
{
sqlComm.CommandText = "UPDATE 库存表 SET 库存下限 = " + dataGridViewDJMX.Rows[i].Cells[4].Value.ToString() + ", 合理库存下限 = " + dataGridViewDJMX.Rows[i].Cells[5].Value.ToString() + ", 合理库存上限 = " + dataGridViewDJMX.Rows[i].Cells[6].Value.ToString() + ", 库存上限 = " + dataGridViewDJMX.Rows[i].Cells[7].Value.ToString() + " WHERE (库房ID = " + intKFID.ToString() + ") AND (商品ID = " + dataGridViewDJMX.Rows[i].Cells[0].Value.ToString() + ")";
sqlComm.ExecuteNonQuery();
}
}
sqlta.Commit();
isSaved = true;
}
catch (Exception ex)
{
MessageBox.Show("数据库错误：" + ex.Message.ToString(), "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlta.Rollback();
return;
}
finally
{
sqlConn.Close();
}
//MessageBox.Show(" 库存上下限定义修改成功", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (MessageBox.Show("库存上下限定义修改成功，是否关闭窗口", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
this.Close();
}
}