|
public static ExecuteNonQuery ( |
||
| connection | ||
| commandText | string | SQL command to be executed |
| Résultat | int | |
private void ApplyNonOptimal(MySqlTransaction trans)
{
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.report_properties
SET
PropertyValue = ?PPropertyValue
WHERE ID = ?PID", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("PID", MySqlDbType.Int64));
UpdCmd.Parameters["PID"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PID"].SourceColumn = PID.ColumnName;
UpdCmd.Parameters["PID"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PPropertyValue", MySqlDbType.VarString));
UpdCmd.Parameters["PPropertyValue"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PPropertyValue"].SourceColumn = PPropertyValue.ColumnName;
UpdCmd.Parameters["PPropertyValue"].SourceVersion = DataRowVersion.Current;
MyDA.UpdateCommand = UpdCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtNonOptionalParams.TableName].TableName);
}
public static void InsertOrUpdateTable(string queryInsert, string queryUpdate, params MySqlParameter[] parameters)
{
// Пробуем вставить строку в таблицу
try {
With.Connection(connection => { MySqlHelper.ExecuteNonQuery(connection, queryInsert, parameters); });
}
catch (Exception) {
// Если не получилось вставить строку, пробуем обновить ее
With.Connection(connection => { MySqlHelper.ExecuteNonQuery(connection, queryUpdate, parameters); });
}
}
public void UpdateLastCheck()
{
LastSuccessfulCheck = DateTime.Now;
With.Connection(c => {
MySqlHelper.ExecuteNonQuery(c, @"
update farm.Sources src
join usersettings.PriceItems pim on src.Id = pim.SourceId
set src.LastSuccessfulCheck = ?LastSuccessfulCheck
where pim.Id = ?PriceItemId",
new MySqlParameter("?PriceItemId", PriceItemId),
new MySqlParameter("?LastSuccessfulCheck", LastSuccessfulCheck));
});
}
public void ApplyChanges(MySqlConnection masterConnection, IProgressNotifier formProgress, List <DataRow> rows)
{
operatorName = Environment.UserName.ToLower();
CalculateChanges(masterConnection, formProgress, rows);
var updateSynonymProducerEtalonSQL = daSynonymFirmCr.UpdateCommand.CommandText;
var insertSynonymProducerEtalonSQL = daSynonymFirmCr.InsertCommand.CommandText;
var changes = dtSynonymFirmCr.GetChanges(DataRowState.Modified);
if (changes != null)
{
stat.SynonymFirmCrCount += changes.Rows.Count;
}
formProgress.Status = "Применение изменений в базу данных...";
DataRow lastUpdateSynonym = null;
try {
With.DeadlockWraper(c => {
var humanName = GetHumanName(c, operatorName);
var helper = new Common.MySql.MySqlHelper(c, null);
var commandHelper = helper.Command("set @inHost = ?Host; set @inUser = ?UserName;");
commandHelper.AddParameter("?Host", Environment.MachineName);
commandHelper.AddParameter("?UserName", operatorName);
commandHelper.Execute();
//Заполнили таблицу логов для синонимов наименований
daSynonym.SelectCommand.Connection = c;
daSynonym.Update(dtSynonym);
formProgress.ApplyProgress += 10;
var insertExclude = new MySqlCommand(@"
insert into Farm.Excludes(CatalogId, PriceCode, ProducerSynonym, DoNotShow, Operator, OriginalSynonymId)
value (?CatalogId, ?PriceCode, ?ProducerSynonym, ?DoNotShow, ?Operator, ?OriginalSynonymId);", c);
insertExclude.Parameters.AddWithValue("?PriceCode", priceId);
insertExclude.Parameters.AddWithValue("?Operator", humanName);
insertExclude.Parameters.Add("?ProducerSynonym", MySqlDbType.VarChar);
insertExclude.Parameters.Add("?DoNotShow", MySqlDbType.Byte);
insertExclude.Parameters.Add("?CatalogId", MySqlDbType.UInt32);
insertExclude.Parameters.Add("?OriginalSynonymId", MySqlDbType.UInt32);
foreach (var exclude in excludes.Where(e => e.Id == 0))
{
if (!IsExcludeCorrect(c, exclude))
{
continue;
}
insertExclude.Parameters["?ProducerSynonym"].Value = exclude.ProducerSynonym;
insertExclude.Parameters["?DoNotShow"].Value = exclude.DoNotShow;
insertExclude.Parameters["?CatalogId"].Value = exclude.CatalogId;
insertExclude.Parameters["?OriginalSynonymId"].Value = exclude.GetOriginalSynonymId();
insertExclude.ExecuteScalar();
}
//Заполнили таблицу логов для синонимов производителей
daSynonymFirmCr.SelectCommand.Connection = c;
daSynonymFirmCr.UpdateCommand.Connection = c;
daSynonymFirmCr.InsertCommand.Connection = c;
var dtSynonymFirmCrCopy = dtSynonymFirmCr.Copy();
foreach (DataRow drInsertProducerSynonym in dtSynonymFirmCrCopy.Rows)
{
lastUpdateSynonym = drInsertProducerSynonym;
daSynonymFirmCr.InsertCommand.CommandText = insertSynonymProducerEtalonSQL;
daSynonymFirmCr.UpdateCommand.CommandText = updateSynonymProducerEtalonSQL;
//обновляем по одному синониму производителя, т.к. может быть добавление в исключение
daSynonymFirmCr.Update(new[] { drInsertProducerSynonym });
}
MySqlHelper.ExecuteNonQuery(c,
@"
update
usersettings.pricescosts,
usersettings.priceitems
set
priceitems.LastSynonymsCreation = now()
where
pricescosts.PriceCode = ?PriceCode
and priceitems.Id = pricescosts.PriceItemId",
new MySqlParameter("?PriceCode", priceId));
formProgress.ApplyProgress += 10;
//Заполнили таблицу логов для запрещённых выражений
daForbidden.SelectCommand.Connection = c;
var dtForbiddenCopy = dtForbidden.Copy();
daForbidden.Update(dtForbiddenCopy);
formProgress.ApplyProgress += 10;
//Обновление таблицы нераспознанных выражений
daUnrecUpdate.SelectCommand.Connection = c;
var dtUnrecUpdateCopy = dtUnrecUpdate.Copy();
daUnrecUpdate.Update(dtUnrecUpdateCopy);
formProgress.ApplyProgress += 10;
// Сохраняем запрещенные имена производителей
var deleteUnrec = new MySqlCommand("delete from farm.UnrecExp where LOWER(FirmCr) = ?FirmName and Status = 1", c);
var insertForbiddenProducer = new MySqlCommand(@"
insert into Farm.Forbiddenproducers(Name)
value (?Name);", c);
insertForbiddenProducer.Parameters.Add("?Name", MySqlDbType.VarChar);
foreach (var producer in ForbiddenProducers.Where(e => e.Id == 0))
{
insertForbiddenProducer.Parameters["?Name"].Value = producer.Name;
insertForbiddenProducer.ExecuteScalar();
// удаляем нераспознанные выражения с таким же наименованием производителя
deleteUnrec.Parameters.Clear();
deleteUnrec.Parameters.AddWithValue("?FirmName", producer.Name.ToLower());
deleteUnrec.ExecuteNonQuery();
}
});
}
catch (Exception e) {
if (e.Message.Contains("Duplicate entry"))
{
Mailer.SendDebugLog(dtSynonymFirmCr, e, lastUpdateSynonym);
}
throw;
}
formProgress.ApplyProgress = 80;
formProgress.Status = String.Empty;
formProgress.Error = String.Empty;
formProgress.Status = "Перепроведение пpайса...";
formProgress.ApplyProgress = 80;
try {
#if !DEBUG
_remotePriceProcessor.RetransPriceSmartMsMq(priceId);
#endif
}
catch (Exception e) {
formProgress.Error = "При перепроведении файлов возникла ошибка, которая отправлена разработчику.";
_logger.Error(String.Format("Ошибка при перепроведении прайс листа {0}", priceId), e);
}
_logger.DebugFormat("Перепроведение пpайса завершено.");
formProgress.ApplyProgress = 100;
}
protected void btnApply_Click(object sender, EventArgs e)
{
Validate();
if (!IsValid)
{
return;
}
CopyChangesToTable();
MySqlTransaction trans;
MyCn.Open();
trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.reports
SET
ReportCaption = ?RReportCaption,
ReportTypeCode = ?RReportTypeCode,
GeneralReportCode = ?RGeneralReportCode,
Enabled = ?REnabled
WHERE ReportCode = ?RReportCode", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString));
UpdCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName;
UpdCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64));
UpdCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName;
UpdCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RReportCode", MySqlDbType.Int64));
UpdCmd.Parameters["RReportCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportCode"].SourceColumn = RReportCode.ColumnName;
UpdCmd.Parameters["RReportCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte));
UpdCmd.Parameters["REnabled"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName;
UpdCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", Request["r"]));
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.reports
WHERE ReportCode = ?RDelReportCode", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("RDelReportCode", MySqlDbType.Int64));
DelCmd.Parameters["RDelReportCode"].Direction = ParameterDirection.Input;
DelCmd.Parameters["RDelReportCode"].SourceColumn = RReportCode.ColumnName;
DelCmd.Parameters["RDelReportCode"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.reports
SET
ReportCaption = ?RReportCaption,
ReportTypeCode = ?RReportTypeCode,
GeneralReportCode = ?RGeneralReportCode,
Enabled = ?REnabled
", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString));
InsCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input;
InsCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName;
InsCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64));
InsCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input;
InsCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName;
InsCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte));
InsCmd.Parameters["REnabled"].Direction = ParameterDirection.Input;
InsCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName;
InsCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", Request["r"]));
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
var strHost = HttpContext.Current.Request.UserHostAddress;
var strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtReports.TableName].TableName);
trans.Commit();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
var report = DbSession.Load <GeneralReport>(Convert.ToUInt64(Request["r"]));
report.EMailSubject = tbEMailSubject.Text;
report.ReportFileName = tbReportFileName.Text;
report.ReportArchName = tbReportArchName.Text;
report.NoArchive = NoArchive.Checked;
report.MailPerFile = MailPerFile.Checked;
report.SendDescriptionFile = SendDescriptionFile.Checked;
report.Format = ReportFormatDD.Text;
DbSession.Save(report);
foreach (GridViewRow dr in fileGridView.Rows)
{
var idField = ((HiddenField)dr.FindControl("Id")).Value;
var property = DbSession.Get <FileSendWithReport>(Convert.ToUInt32(idField));
var file = ((FileUpload)dr.FindControl("UploadFile"));
if (file.HasFile)
{
property.FileName = file.FileName;
File.WriteAllBytes(property.FileNameForSave, file.FileBytes);
DbSession.Save(property);
}
}
PostData();
}
/// <summary>
/// Дублирует все значения свойств из одного отчета в другой (клонирование)
/// </summary>
/// <param name="sourceReportId">код исходного отчета из таблицы reports.reports</param>
/// <param name="destinationReportId">код отчета-приемника из таблицы reports.reports</param>
public static void CopyReportProperties(ulong sourceReportId, ulong destinationReportId)
{
Report _sourceReport = Report.Find(sourceReportId);
Report _destinationReport = Report.Find(destinationReportId);
if (_sourceReport.ReportType != _destinationReport.ReportType)
{
throw new ReportTunerException(
String.Format(
"Тип клонируемого отчета отличается от конечного отчета. Тип исходного отчета: {0}. Тип отчета-приемника: {1}",
_sourceReport.ReportType.ReportTypeName,
_destinationReport.ReportType.ReportTypeName));
}
DataSet dsReportProperties = MySqlHelper.ExecuteDataset(
ConnectionHelper.GetConnectionString(),
@"
SELECT
rp.*,
rtp.PropertyType
FROM
reports.report_properties rp,
reports.report_type_properties rtp
where
rp.ReportCode = ?SourceReportId
and rtp.Id = rp.PropertyId
order by rp.PropertyID;
SELECT
rpv.*
FROM
reports.report_properties rp,
reports.report_property_values rpv
where
rp.ReportCode = ?SourceReportId
and rpv.ReportPropertyId = rp.Id;
SELECT
rp.*,
rtp.PropertyType
FROM
reports.report_properties rp,
reports.report_type_properties rtp
where
rp.ReportCode = ?DestinationReportId
and rtp.Id = rp.PropertyId
order by rp.PropertyID;
",
new MySqlParameter("?SourceReportId", sourceReportId),
new MySqlParameter("?DestinationReportId", destinationReportId));
DataTable dtSourceProperties = dsReportProperties.Tables[0];
DataTable dtSourcePropertiesValues = dsReportProperties.Tables[1];
DataTable dtDestinationProperties = dsReportProperties.Tables[2];
StringBuilder sbCommand = new StringBuilder();
foreach (DataRow drSourceProperty in dtSourceProperties.Rows)
{
DataRow[] drDestinationProperties = dtDestinationProperties.Select("PropertyId = " + drSourceProperty["PropertyId"]);
if (drDestinationProperties.Length == 0)
{
//Свойство не существует, поэтому просто вставляем новое
sbCommand.AppendFormat("insert into reports.report_properties (ReportCode, PropertyId, PropertyValue) values ({0}, {1}, '{2}');\r\n",
destinationReportId, drSourceProperty["PropertyId"], drSourceProperty["PropertyValue"]);
if (drSourceProperty["PropertyType"].ToString().Equals("LIST", StringComparison.OrdinalIgnoreCase))
{
sbCommand.AppendLine("set @LastReportPropertyId = last_insert_id();");
foreach (DataRow drSourcePropertiesValue in dtSourcePropertiesValues.Select("ReportPropertyId = " + drSourceProperty["Id"]))
{
sbCommand.AppendFormat("insert into reports.report_property_values (ReportPropertyId, Value) values (@LastReportPropertyId, '{0}');\r\n",
drSourcePropertiesValue["Value"]);
}
}
}
else
{
//Свойство существует, поэтому обновляем запись
sbCommand.AppendFormat("update reports.report_properties set PropertyValue = '{0}' where Id = {1};\r\n",
drSourceProperty["PropertyValue"], drDestinationProperties[0]["Id"]);
if (drSourceProperty["PropertyType"].ToString().Equals("LIST", StringComparison.OrdinalIgnoreCase))
{
sbCommand.AppendFormat("delete from reports.report_property_values where ReportPropertyId = {0};\r\n", drDestinationProperties[0]["Id"]);
foreach (DataRow drSourcePropertiesValue in dtSourcePropertiesValues.Select("ReportPropertyId = " + drSourceProperty["Id"]))
{
sbCommand.AppendFormat("insert into reports.report_property_values (ReportPropertyId, Value) values ({0}, '{1}');\r\n",
drDestinationProperties[0]["Id"], drSourcePropertiesValue["Value"]);
}
}
}
}
MySqlConnection connection = new MySqlConnection(ConnectionHelper.GetConnectionString());
connection.Open();
try {
MySqlTransaction transaction = connection.BeginTransaction();
MySqlHelper.ExecuteNonQuery(connection, sbCommand.ToString());
transaction.Commit();
}
finally {
connection.Close();
}
}
protected void btnApply_Click(object sender, EventArgs e)
{
CopyChangesToTable();
var _deletedReports = new List <ulong>();
var _updatedReports = new List <ulong>();
DataTable dtInserted;
MyCn.Open();
var trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
var UpdCmd = new MySqlCommand(@"
UPDATE
reports.general_reports
SET
Allow = ?Allow,
Public = ?Public,
Comment = ?Comment,
FirmCode = if(PayerID = ?payerID, FirmCode,
(select min(Id)
from
(select s.Id
from Customers.Suppliers s
where s.Payer = ?payerID) tbl)),
PayerID = ?payerID
WHERE GeneralReportCode = ?GeneralReportCode", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("Allow", MySqlDbType.Byte));
UpdCmd.Parameters["Allow"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["Allow"].SourceColumn = Allow.ColumnName;
UpdCmd.Parameters["Allow"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("Public", MySqlDbType.Byte));
UpdCmd.Parameters["Public"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["Public"].SourceColumn = Public.ColumnName;
UpdCmd.Parameters["Public"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("Comment", MySqlDbType.VarString));
UpdCmd.Parameters["Comment"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["Comment"].SourceColumn = Comment.ColumnName;
UpdCmd.Parameters["Comment"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("GeneralReportCode", MySqlDbType.Int64));
UpdCmd.Parameters["GeneralReportCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["GeneralReportCode"].SourceColumn = GeneralReportCode.ColumnName;
UpdCmd.Parameters["GeneralReportCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add("?payerID", MySqlDbType.Int64).SourceColumn = GRPayerID.ColumnName;
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.general_reports
WHERE GeneralReportCode = ?GRDelCode", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("GRDelCode", MySqlDbType.Int64));
DelCmd.Parameters["GRDelCode"].Direction = ParameterDirection.Input;
DelCmd.Parameters["GRDelCode"].SourceColumn = GeneralReportCode.ColumnName;
DelCmd.Parameters["GRDelCode"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.general_reports
(PayerId, Allow, Public, Comment, FirmCode)
select
?PayerId,
?Allow,
?Public,
?Comment,
min(Id)
from
(
select s.Id
from Customers.Suppliers s
where s.Payer = ?payerID
) tbl;
select last_insert_id() as GRLastInsertID;
", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("Allow", MySqlDbType.Byte));
InsCmd.Parameters["Allow"].Direction = ParameterDirection.Input;
InsCmd.Parameters["Allow"].SourceColumn = Allow.ColumnName;
InsCmd.Parameters["Allow"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("Public", MySqlDbType.Byte));
InsCmd.Parameters["Public"].Direction = ParameterDirection.Input;
InsCmd.Parameters["Public"].SourceColumn = Public.ColumnName;
InsCmd.Parameters["Public"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PayerId", MySqlDbType.Int64));
InsCmd.Parameters["PayerId"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PayerId"].SourceColumn = GRPayerID.ColumnName;
InsCmd.Parameters["PayerId"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("Comment", MySqlDbType.VarString));
InsCmd.Parameters["Comment"].Direction = ParameterDirection.Input;
InsCmd.Parameters["Comment"].SourceColumn = Comment.ColumnName;
InsCmd.Parameters["Comment"].SourceVersion = DataRowVersion.Current;
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
DataTable dtDeleted = DS.Tables[dtGeneralReports.TableName].GetChanges(DataRowState.Deleted);
if (dtDeleted != null)
{
foreach (DataRow drDeleted in dtDeleted.Rows)
{
var code = Convert.ToUInt64(drDeleted[GeneralReportCode.ColumnName, DataRowVersion.Original]);
var comment = drDeleted[Comment.ColumnName, DataRowVersion.Original].ToString();
SendDeleteAlert(code, comment, strUser, strHost);
_deletedReports.Add(code);
}
MyDA.Update(dtDeleted);
}
dtInserted = DS.Tables[dtGeneralReports.TableName].GetChanges(DataRowState.Added);
if (dtInserted != null)
{
foreach (DataRow drInsert in dtInserted.Rows)
{
if (!Convert.IsDBNull(drInsert[GRPayerID.ColumnName]) && (drInsert[GRPayerID.ColumnName] is long))
{
MyDA.Update(new DataRow[] { drInsert });
_updatedReports.Add(Convert.ToUInt64(drInsert["GRLastInsertID"]));
}
}
}
DataTable dtUpdated = DS.Tables[dtGeneralReports.TableName].GetChanges(DataRowState.Modified);
if (dtUpdated != null)
{
foreach (DataRow drUpdate in dtUpdated.Rows)
{
if (drUpdate["Comment", DataRowVersion.Original] != drUpdate["Comment", DataRowVersion.Current] ||
drUpdate["Public", DataRowVersion.Original] != drUpdate["Public", DataRowVersion.Current] ||
drUpdate["Allow", DataRowVersion.Original] != drUpdate["Allow", DataRowVersion.Current])
{
_updatedReports.Add(Convert.ToUInt64(drUpdate["GeneralReportCode"]));
}
}
MyDA.Update(dtUpdated);
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
//Удаляем задания для отчетов и обновляем комментарии в заданиях (или создаем эти задания)
// А также включаем/выключаем задание при изменении галки "Включен"
UpdateTasksForGeneralReports(_deletedReports, _updatedReports);
PostData();
if (dgvReports.Rows.Count > 0)
{
btnApply.Visible = true;
}
else
{
btnApply.Visible = false;
}
if (dtInserted != null)
{
if (!Request.Url.OriginalString.Contains("#"))
{
Response.Redirect(Request.Url.OriginalString + "#addedPage");
}
}
}
protected void btnApply_Click(object sender, EventArgs e)
{
CopyChangesToTable();
MySqlTransaction trans;
MyCn.Open();
trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.enum_values
SET
Value = ?evValue,
DisplayValue = ?evDisplayValue
WHERE ID = ?evID", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("evValue", MySqlDbType.VarString));
UpdCmd.Parameters["evValue"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["evValue"].SourceColumn = evValue.ColumnName;
UpdCmd.Parameters["evValue"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("evDisplayValue", MySqlDbType.VarString));
UpdCmd.Parameters["evDisplayValue"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["evDisplayValue"].SourceColumn = evDisplayValue.ColumnName;
UpdCmd.Parameters["evDisplayValue"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("evID", MySqlDbType.Int64));
UpdCmd.Parameters["evID"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["evID"].SourceColumn = evID.ColumnName;
UpdCmd.Parameters["evID"].SourceVersion = DataRowVersion.Current;
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.enum_values
WHERE ID = ?evDelID", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("evDelID", MySqlDbType.Int64));
DelCmd.Parameters["evDelID"].Direction = ParameterDirection.Input;
DelCmd.Parameters["evDelID"].SourceColumn = evID.ColumnName;
DelCmd.Parameters["evDelID"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.enum_values
SET
Value = ?evValue,
DisplayValue = ?evDisplayValue,
PropertyEnumID = ?evPEID", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("evValue", MySqlDbType.VarString));
InsCmd.Parameters["evValue"].Direction = ParameterDirection.Input;
InsCmd.Parameters["evValue"].SourceColumn = evValue.ColumnName;
InsCmd.Parameters["evValue"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("evDisplayValue", MySqlDbType.VarString));
InsCmd.Parameters["evDisplayValue"].Direction = ParameterDirection.Input;
InsCmd.Parameters["evDisplayValue"].SourceColumn = evDisplayValue.ColumnName;
InsCmd.Parameters["evDisplayValue"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("evPEID", Request["e"]));
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtEnumValues.TableName].TableName);
trans.Commit();
PostData();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
if (dgvEnumValues.Rows.Count > 0)
{
btnApply.Visible = true;
}
else
{
btnApply.Visible = false;
}
}
public virtual List <Offer> GetOffers(int clientId, uint sourcePriceCode, uint?noiseSupplierId, bool allAssortment, bool byCatalog, bool withProducers)
{
ClientCode = clientId;
InvokeGetActivePrices();
var assortmentSupplierId = Convert.ToUInt32(
MySqlHelper.ExecuteScalar(Connection,
@"
select FirmCode
from usersettings.pricesdata
where pricesdata.PriceCode = ?PriceCode
",
new MySqlParameter("?PriceCode", sourcePriceCode)));
//Заполняем код региона прайс-листа как домашний код региона клиента, относительно которого строится отчет
var SourceRegionCode = Convert.ToUInt64(
MySqlHelper.ExecuteScalar(Connection,
@"
select RegionCode
from Customers.Clients
where Id = ?ClientCode",
new MySqlParameter("?ClientCode", ClientCode)));
var enabledCost = MySqlHelper.ExecuteScalar(
Connection,
"select CostCode from ActivePrices where PriceCode = ?SourcePC and RegionCode = ?SourceRegionCode",
new MySqlParameter("?SourcePC", sourcePriceCode),
new MySqlParameter("?SourceRegionCode", SourceRegionCode));
if (enabledCost != null)
{
MySqlHelper.ExecuteNonQuery(
Connection,
@"
drop temporary table IF EXISTS Usersettings.SourcePrice;
create temporary table Usersettings.SourcePrice engine=MEMORY
select * from ActivePrices where PriceCode = ?SourcePC and RegionCode = ?SourceRegionCode;",
new MySqlParameter("?SourcePC", sourcePriceCode),
new MySqlParameter("?SourceRegionCode", SourceRegionCode));
}
var joinText = allAssortment || sourcePriceCode == 0 ? " Left JOIN " : " JOIN ";
string withWithoutPropertiesText;
if (byCatalog)
{
withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", GetCatalogProductNameSubquery("p.id"));
}
else
{
withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", QueryParts.GetFullFormSubquery("p.id", true));
}
var firmcr = withProducers ? " and ifnull(C0.CodeFirmCr,0) = ifnull(c00.CodeFirmCr,0) " : string.Empty;
var producerId = withProducers ? " ifnull(c00.CodeFirmCr, 0) " : " 0 ";
var producerName = withProducers ? " if(c0.SynonymFirmCrCode is not null, Sfc.Synonym , Prod.Name) " : " '-' ";
var result = new List <Offer>();
DataAdapter.SelectCommand.CommandText =
string.Format(
@"
select
p.CatalogId,
c00.ProductId,
{0} as ProducerId,
{1} as ProductName,
{2} as ProducerName,
c00.Id as CoreId,
c00.Code,
Prices.FirmCode as SupplierId,
c00.PriceCode as PriceId,
Prices.RegionCode as RegionId,
c00.Quantity,
if(if(round(cc.Cost * Prices.Upcost, 2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2)) > c00.MaxBoundCost,
c00.MaxBoundCost, if(round(cc.Cost*Prices.UpCost,2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2))) as Cost,
c0.Id as AssortmentCoreId,
c0.Code as AssortmentCode,
c0.CodeCr as AssortmentCodeCr,
{9} as AssortmentSupplierId,
c0.PriceCode as AssortmentPriceId,
{10} as AssortmentRegionId,
c0.Quantity as AssortmentQuantity,
{7} as AssortmentCost
from
Usersettings.ActivePrices Prices
join farm.core0 c00 on c00.PriceCode = Prices.PriceCode
join farm.CoreCosts cc on cc.Core_Id = c00.Id and cc.PC_CostCode = Prices.CostCode
join catalogs.Products as p on p.id = c00.productid
join Catalogs.Catalog as cg on p.catalogid = cg.id
{3} farm.Core0 c0 on c0.productid = c00.productid {4} and C0.PriceCode = {5}
{6}
left join Catalogs.Producers Prod on c00.CodeFirmCr = Prod.Id
left join farm.Synonym S on C0.SynonymCode = S.SynonymCode
left join farm.SynonymFirmCr Sfc on C0.SynonymFirmCrCode = Sfc.SynonymFirmCrCode
{8}
WHERE
{11}
",
producerId,
withWithoutPropertiesText,
producerName,
joinText,
firmcr,
sourcePriceCode,
(enabledCost != null)
? @"
left join farm.CoreCosts cc0 on cc0.Core_Id = c0.Id and cc0.PC_CostCode = " + enabledCost + @"
left join Usersettings.SourcePrice c0Prices on c0Prices.CostCode = " + enabledCost
: "",
(enabledCost != null)
? @"
if(cc0.Cost is null, 0,
if(if(round(cc0.Cost * c0Prices.Upcost, 2) < c0.MinBoundCost, c0.MinBoundCost, round(cc0.Cost * c0Prices.Upcost, 2)) > c0.MaxBoundCost,
c0.MaxBoundCost, if(round(cc0.Cost*c0Prices.UpCost,2) < c0.MinBoundCost, c0.MinBoundCost, round(cc0.Cost * c0Prices.Upcost, 2)))
)"
: " null ",
@"",
assortmentSupplierId,
SourceRegionCode,
sourcePriceCode == 0
? " c00.Junk = 0 "
: @"
({1} (c0.PriceCode <> c00.PriceCode) or (Prices.RegionCode <> {0}) or (c0.Id = c00.Id))
and (c00.Junk = 0 or c0.Id = c00.Id)".Format(SourceRegionCode, allAssortment || sourcePriceCode == 0 ? "(c0.PriceCode is null) or" : string.Empty));
Random random = null;
if (noiseSupplierId.HasValue)
{
random = new Random();
}
#if DEBUG
Debug.WriteLine(DataAdapter.SelectCommand.CommandText);
#endif
using (var reader = DataAdapter.SelectCommand.ExecuteReader()) {
foreach (var row in reader.Cast <IDataRecord>())
{
var offer = new Offer(row, noiseSupplierId, random);
result.Add(offer);
}
}
return(result);
}
protected void btnApply_Click(object sender, EventArgs e)
{
CopyChangesToTable();
MySqlTransaction trans;
MyCn.Open();
trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.reports
SET
ReportCaption = ?RReportCaption,
ReportTypeCode = ?RReportTypeCode,
GeneralReportCode = ?RGeneralReportCode,
Enabled = ?REnabled
WHERE ReportCode = ?RReportCode", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString));
UpdCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName;
UpdCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64));
UpdCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName;
UpdCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RReportCode", MySqlDbType.Int64));
UpdCmd.Parameters["RReportCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportCode"].SourceColumn = RReportCode.ColumnName;
UpdCmd.Parameters["RReportCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte));
UpdCmd.Parameters["REnabled"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName;
UpdCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", _templateReportId));
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.reports
WHERE ReportCode = ?RDelReportCode", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("RDelReportCode", MySqlDbType.Int64));
DelCmd.Parameters["RDelReportCode"].Direction = ParameterDirection.Input;
DelCmd.Parameters["RDelReportCode"].SourceColumn = RReportCode.ColumnName;
DelCmd.Parameters["RDelReportCode"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.reports
SET
ReportCaption = ?RReportCaption,
ReportTypeCode = ?RReportTypeCode,
GeneralReportCode = ?RGeneralReportCode,
Enabled = ?REnabled
", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString));
InsCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input;
InsCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName;
InsCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64));
InsCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input;
InsCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName;
InsCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte));
InsCmd.Parameters["REnabled"].Direction = ParameterDirection.Input;
InsCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName;
InsCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", _templateReportId));
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtReports.TableName].TableName);
trans.Commit();
PostData();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
if (dgvReports.Rows.Count > 0)
{
btnApply.Visible = true;
}
else
{
btnApply.Visible = false;
}
}
protected void btnApply_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
CopyChangesToTable();
MySqlTransaction trans;
MyCn.Open();
trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.report_type_properties
SET
PropertyName = ?PName,
DisplayName = ?PDisplayName,
DefaultValue = ?PDefaultValue,
PropertyType = ?PType,
Optional = ?POptional,
PropertyEnumID = ?PEnumID,
SelectStoredProcedure = ?PStoredProc
WHERE ID = ?PID", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("PName", MySqlDbType.VarString));
UpdCmd.Parameters["PName"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PName"].SourceColumn = PName.ColumnName;
UpdCmd.Parameters["PName"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PDisplayName", MySqlDbType.VarString));
UpdCmd.Parameters["PDisplayName"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PDisplayName"].SourceColumn = PDisplayName.ColumnName;
UpdCmd.Parameters["PDisplayName"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PDefaultValue", MySqlDbType.VarString));
UpdCmd.Parameters["PDefaultValue"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PDefaultValue"].SourceColumn = PDefaultValue.ColumnName;
UpdCmd.Parameters["PDefaultValue"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PType", MySqlDbType.VarString));
UpdCmd.Parameters["PType"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PType"].SourceColumn = PType.ColumnName;
UpdCmd.Parameters["PType"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("POptional", MySqlDbType.Byte));
UpdCmd.Parameters["POptional"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["POptional"].SourceColumn = POptional.ColumnName;
UpdCmd.Parameters["POptional"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PEnumID", MySqlDbType.Int64));
UpdCmd.Parameters["PEnumID"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PEnumID"].SourceColumn = PEnumID.ColumnName;
UpdCmd.Parameters["PEnumID"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PStoredProc", MySqlDbType.VarString));
UpdCmd.Parameters["PStoredProc"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PStoredProc"].SourceColumn = PStoredProc.ColumnName;
UpdCmd.Parameters["PStoredProc"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PID", MySqlDbType.Int64));
UpdCmd.Parameters["PID"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PID"].SourceColumn = PID.ColumnName;
UpdCmd.Parameters["PID"].SourceVersion = DataRowVersion.Current;
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.report_type_properties
WHERE ID = ?PDelID", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("PDelID", MySqlDbType.Int64));
DelCmd.Parameters["PDelID"].Direction = ParameterDirection.Input;
DelCmd.Parameters["PDelID"].SourceColumn = PID.ColumnName;
DelCmd.Parameters["PDelID"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.report_type_properties
SET
PropertyName = ?PName,
DisplayName = ?PDisplayName,
DefaultValue = ?PDefaultValue,
PropertyType = ?PType,
PropertyEnumID = ?PEnumID,
Optional = ?POptional,
SelectStoredProcedure = ?PStoredProc,
ReportTypeCode = ?rtc
", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("PName", MySqlDbType.VarString));
InsCmd.Parameters["PName"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PName"].SourceColumn = PName.ColumnName;
InsCmd.Parameters["PName"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PDisplayName", MySqlDbType.VarString));
InsCmd.Parameters["PDisplayName"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PDisplayName"].SourceColumn = PDisplayName.ColumnName;
InsCmd.Parameters["PDisplayName"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PDefaultValue", MySqlDbType.VarString));
InsCmd.Parameters["PDefaultValue"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PDefaultValue"].SourceColumn = PDefaultValue.ColumnName;
InsCmd.Parameters["PDefaultValue"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PType", MySqlDbType.VarString));
InsCmd.Parameters["PType"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PType"].SourceColumn = PType.ColumnName;
InsCmd.Parameters["PType"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("POptional", MySqlDbType.Byte));
InsCmd.Parameters["POptional"].Direction = ParameterDirection.Input;
InsCmd.Parameters["POptional"].SourceColumn = POptional.ColumnName;
InsCmd.Parameters["POptional"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PEnumID", MySqlDbType.Int64));
InsCmd.Parameters["PEnumID"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PEnumID"].SourceColumn = PEnumID.ColumnName;
InsCmd.Parameters["PEnumID"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PStoredProc", MySqlDbType.VarString));
InsCmd.Parameters["PStoredProc"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PStoredProc"].SourceColumn = PStoredProc.ColumnName;
InsCmd.Parameters["PStoredProc"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("rtc", Request["rtc"]));
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtProperties.TableName].TableName);
trans.Commit();
PostData();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
btnApply.Visible = dgvProperties.Rows.Count > 0;
}
}
/**
* 从雅虎获取股票历史数据存款数据库
* */
public static void loadAllHisDataFromYaohu(DateTime bdate, DateTime edate)
{
string gpcodes = getGpsForLoadYaohHis();
char[] ch = new char[] { ',' };
string[] gpcodeList = gpcodes.Split(ch);
string code = "";
DataTable hisData = null;
MySqlHelper help = new MySqlHelper();
//已经保存的历史数据
MySqlParameter[] parms = new MySqlParameter[] { };
DataTable codeDatas = help.ExecuteDataTable("SELECT DISTINCT(g.Code) FROM gpyaohhis g where g.AdjClose IS NOT NULL", parms);
StringBuilder sb = new StringBuilder();
foreach (DataRow row in codeDatas.Rows)
{
code = row["code"].ToString();
if (code.IndexOf("sh") != -1)
{
code = code.Replace("sh", "");
code = code + ".ss";
}
else
{
code = code.Replace("sz", "");
code = code + ".sz";
}
sb.Append(code).Append(",");
}
string saveCodes = sb.ToString();
if (!string.IsNullOrEmpty(saveCodes))
{
saveCodes = saveCodes.Substring(0, saveCodes.LastIndexOf(","));
}
//Console.WriteLine("####saveCodes:[" + saveCodes + "]");
//个股http://table.finance.yahoo.com/table.csv?a=9&b=24&c=2015&d=10&e=23&f=2015&s=603883.ss
string tmpCode = "";
string oldCode = "";
string excodes = "399006,603021";
for (int i = 0; i < gpcodeList.Length; i++)
{
code = gpcodeList[i];
oldCode = code;
tmpCode = code.Replace("sh", "").Replace("sz", "");
if (String.IsNullOrEmpty(code))
{
continue;
}
if (code.IndexOf("sh") != -1)
{
code = code.Replace("sh", "");
code = code + ".ss";
}
else
{
code = code.Replace("sz", "");
code = code + ".sz";
}
if (saveCodes.IndexOf(code) != -1 || excodes.IndexOf(tmpCode) != -1)
{
continue;
}
Console.WriteLine("开始[" + code + "]");
//历史数据
try
{
hisData = SinaAPI.getGuoPiaoHisDataFromYaoh(bdate, edate, code);
}
catch (Exception e)
{
Console.Write(e.Message.ToString());
Console.Write("update cnt:"+help.ExecuteNonQuery("update gp set updhis='1' where code='" + oldCode + "'"));
continue;
}
if (hisData == null || hisData.Rows.Count <= 0)
{
return;
}
Console.Write(hisData.Rows.Count.ToString());
//保存到数据库
hisData.TableName = "gpyaohhis";
MySqlHelper.BulkInsert(help.ConnectionString, hisData);
Console.WriteLine("结束[" + code + "]");
}
}
private void ApplyOptimal(MySqlTransaction trans)
{
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.report_properties
SET
PropertyValue = ?OPPropertyValue
WHERE ID = ?OPID", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("OPID", MySqlDbType.Int64));
UpdCmd.Parameters["OPID"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["OPID"].SourceColumn = OPID.ColumnName;
UpdCmd.Parameters["OPID"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("OPPropertyValue", MySqlDbType.VarString));
UpdCmd.Parameters["OPPropertyValue"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["OPPropertyValue"].SourceColumn = OPPropertyValue.ColumnName;
UpdCmd.Parameters["OPPropertyValue"].SourceVersion = DataRowVersion.Current;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT
reports.report_properties
SET
ReportCode = ?rp,
PropertyID = ?OPrtpID,
PropertyValue = ?OPPropertyValue
", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("OPID", MySqlDbType.Int64));
InsCmd.Parameters["OPID"].Direction = ParameterDirection.Input;
InsCmd.Parameters["OPID"].SourceColumn = OPID.ColumnName;
InsCmd.Parameters["OPID"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("OPPropertyValue", MySqlDbType.VarString));
InsCmd.Parameters["OPPropertyValue"].Direction = ParameterDirection.Input;
InsCmd.Parameters["OPPropertyValue"].SourceColumn = OPPropertyValue.ColumnName;
InsCmd.Parameters["OPPropertyValue"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("OPrtpID", MySqlDbType.Int64));
InsCmd.Parameters["OPrtpID"].Direction = ParameterDirection.Input;
InsCmd.Parameters["OPrtpID"].SourceColumn = OPrtpID.ColumnName;
InsCmd.Parameters["OPrtpID"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("rp", Request["rp"]));
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE FROM
reports.report_properties
WHERE ID = ?OPID", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("OPID", MySqlDbType.Int64));
DelCmd.Parameters["OPID"].Direction = ParameterDirection.Input;
DelCmd.Parameters["OPID"].SourceColumn = OPID.ColumnName;
DelCmd.Parameters["OPID"].SourceVersion = DataRowVersion.Original;
MyDA.UpdateCommand = UpdCmd;
MyDA.InsertCommand = InsCmd;
MyDA.DeleteCommand = DelCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtOptionalParams.TableName].TableName);
}
protected void btnApply_Click(object sender, EventArgs e)
{
CopyChangesToTable();
string ins = String.Empty;
string del = String.Empty;
foreach (DataRow dr in DS.Tables[dtProcResult.TableName].Rows)
{
if (dr.RowState == DataRowState.Modified)
{
if (dr[Enabled.ColumnName, DataRowVersion.Original].ToString() == dr[Enabled.ColumnName, DataRowVersion.Current].ToString())
{
dr.RejectChanges();
}
}
}
MySqlTransaction trans;
MyCn.Open();
trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
MySqlCommand UpdCmd = new MySqlCommand(@"
insert into reports.report_property_values
(ReportPropertyID, Value)
select r.ID, ?Value
from
reports.report_properties r
where
r.ID = ?RPID
and ?Enabled = 1;
delete from reports.report_property_values
where
ReportPropertyID = ?RPID
and Value = ?Value
and ?Enabled = 0;", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("Value", MySqlDbType.Int64));
UpdCmd.Parameters["Value"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["Value"].SourceColumn = PRID.ColumnName;
UpdCmd.Parameters["Value"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("Enabled", MySqlDbType.Byte));
UpdCmd.Parameters["Enabled"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["Enabled"].SourceColumn = Enabled.ColumnName;
UpdCmd.Parameters["Enabled"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RPID", property.Id));
MyDA.UpdateCommand = UpdCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtProcResult.TableName].TableName);
trans.Commit();
DS.Tables[dtProcResult.TableName].AcceptChanges();
PostData();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
if (dgvListValues.Rows.Count > 0)
{
btnApply.Visible = true;
}
else
{
btnApply.Visible = false;
}
}
