public async Task <int> SetAsPayed(string order_no, string transaction_id, PayMethodEnum method)
{
var pay_time = DateTime.UtcNow;
var sql = @$ "
update {table} set
Status=@payed_status,
PayTime=@pay_time,
ExternalPaymentNo=@transaction_id,
PayMethod=@pay_method
where OrderNo=@order_no and Status=@where_status
";
var p = new
{
payed_status = (int)OrderStatusEnum.待设计,
where_status = (int)OrderStatusEnum.待付款,
pay_method = (int)method,
order_no = order_no,
pay_time = pay_time,
transaction_id = transaction_id,
};
using (var con = this._dbFactory.GetMetroAdDatabase())
{
var count = await con.ExecuteAsync(sql, p);
return(count);
}
}
private static PointF PointFromWhere(where wh, SizeF sizef)
{
PointF point = new PointF();
switch (wh)
{
case where.NONE:
point = new Point(-10, -10);
break;
case where.TOP_LEFT:
point = new PointF((float)(mousePos.X + 2), (float)(mousePos.Y + 2));
break;
case where.TOP_RIGHT:
point = new PointF((float)(mousePos.X - sizef.Width - 2), (float)(mousePos.Y + 2));
break;
case where.BOTTOM_LEFT:
point = new PointF((float)(mousePos.X + 2), (float)(mousePos.Y - sizef.Height - 2));
break;
case where.BOTTOM_RIGHT:
point = new PointF((float)(mousePos.X - sizef.Width - 2), (float)(mousePos.Y - sizef.Height - 2));
break;
}
return(point);
}
public void ChangeValue(string newValue)
{
Collection[0].Result = newValue;
using SQLiteConnection dataBase = new SQLiteConnection($"Data Source=Calc.db; Version=3");
dataBase.Query(@$ "Update Memory
Set Result = {newValue} where Id = {Collection.Count()}");
}
public bool Editar(Usuario entidad)
{
using Conexion conexion = new Conexion();
string consulta = @$ "
update usuario set
nombre = @Nombre,
apellido = @Apellido,
correo = @Correo,
clave = @Clave,
telefono = @Telefono,
cargo = @Cargo,
activo = @Activo,
actualizado = curdate()
where id = @Id
" ;
var usuario = PorId(entidad.Id);
if (entidad.Clave == null)
{
entidad.Clave = usuario.Clave;
}
int filasAfectadas = conexion.Ejecutar(consulta, entidad);
return(filasAfectadas > 0);
}
public static void Main()
{
int n;
@int n2 = new @int();
n2.show();
where w = new where ();
}
public bool Editar(Entrada entidad)
{
using Conexion conexion = new Conexion();
string consulta = @$ "
update entrada set fecha = @Fecha, observacion = @Observacion
where id = @Id" ;
int filasAfectadas = conexion.Ejecutar(consulta, entidad);
return(filasAfectadas > 0);
}
public async Task <IEnumerable <DataLeituraAluno> > ObterDadosLeituraAlunos(long notificacaoId, string codigosAlunos)
{
var sql =
@$ "
select
codigo_eol_aluno CodigoAluno,
greatest(criadoem, alteradoem) DataLeitura
from
usuario_notificacao_leitura unl
where
notificacao_id = {notificacaoId} and
codigo_eol_aluno in ({codigosAlunos})
public async Task <TradingEngineSnapshot> Get(string correlationId)
{
var sql = @$ "select top(1) TradingDay,
CorrelationId,
Orders as OrdersJson,
Positions as PositionsJson,
AccountStats as AccountsJson,
BestFxPrices as BestFxPricesJson,
BestPrices as BestTradingPricesJson,
Timestamp
from {TableName} where correlationId = @id
and status = '{nameof(SnapshotStatus.Final)}'
public async Task UpdatePayCounter(string order_uid)
{
var sql = @$ "update {table} set
`PayCounter`=`PayCounter`+1
where UID=@uid";
using (var db = this._dbFactory.GetMetroAdDatabase())
{
var rows = await db.ExecuteAsync(sql, new { uid = order_uid });
rows.Should().BeGreaterThan(0);
}
}
public async Task Save(ComplexityWarningState entity)
{
await using var conn = new SqlConnection(_connectionString);
var propsToUpdate = typeof(DbSchema).GetProperties().Where(p => p.Name != nameof(DbSchema.RowVersion));
var updateStatement = string.Join(",", propsToUpdate.Select(x => "[" + x.Name + "]=@" + x.Name));
//Dapper.Contrib does not support optimistic concurrency, that's why we have to write plain sql here instead of Update
//see https://github.com/StackExchange/Dapper/issues/851
var sql = @$ "
update dbo.MarginTradingAccountsComplexityWarnings
set {updateStatement}
where AccountId = @{nameof(DbSchema.AccountId)} and RowVersion = @{nameof(DbSchema.RowVersion)}
public IEnumerable <LineItemWithProduct> GetLineItemsWithProduct(int orderId)
{
using var db = new SqlConnection(_connectionString);
var query = @$ "select *
from LineItem
join Product
on LineItem.ProductId = Product.ProductId
where PurchaseOrderId = @Id";
var parameters = new { Id = orderId };
var items = db.Query <LineItemWithProduct>(query, parameters);
return(items);
}
public static Task <IReadOnlyCollection <string> > MissingUsers(this YtCollectDbCtx ctx, int?limit = null)
{
limit ??= ctx.YtCfg.MaxMissingUsers;
return(ctx.Db.Query <string>("missing users", @$ "
select distinct author_channel_id
from comment t
join video_latest v on v.video_id = t.video_id
where
platform = 'YouTube'
and not exists(select * from user where user_id = author_channel_id)
{limit.Dot(i => $" limit {
i
} ")}
"));
}
public void Remove(MemoryItem item)
{
Collection.Remove(item);
for (int i = 0; i < Collection.Count; i++)
{
Collection[i].Id = (Collection.Count - i).ToString();
}
using SQLiteConnection dataBase = new SQLiteConnection($"Data Source=Calc.db; Version=3");
dataBase.Query(@$ "Delete from Memory
Where Id = {item.Id}");
for (int i = int.Parse(item.Id) + 1; i <= Collection.Count + 1; i++)
{
dataBase.Query(@$ "Update Memory
Set Id = {i - 1} where Id = {i}");
}
}
private string GetScript()
{
return(@$ "
declare @mainCode nvarchar(10) = @UserCode,
@notMainCode nvarchar(10),
@fullName nvarchar(max),
@mainId int,
@notMainId int
select @fullName = Full_Name from People
where Person_Code = @mainCode
select @notMainCode = Person_Code from People
where Full_Name = @fullName and Person_Code != @mainCode
select @mainId = Person_ID from People
where Person_Code = @mainCode
select @notMainId = Person_ID from People
where Person_Code = @notMainCode
select @mainCode as MainCode, @notMainCode as NotMainCode, @fullName as FullName, @mainId as MainId, @notMainId as NotMainId
update RegForms_Headers
set Person_ID = @mainId
where Person_ID = @notMainId
update Pending_RegForms_Headers
set Person_ID = @mainId
where Person_ID = @notMainId
update Mail_Delivery
set Person_ID = @mainId
where Person_ID = @notMainId
update LearningCourses
set Person_ID = @mainId
where Person_ID = @notMainId
update People_Photos
set Person_ID = @mainId
where Person_ID = @notMainId
delete from People_Contacts
where Person_ID = @notMainId
delete from People
where Person_ID = @notMainId
");
}
public Task <User> Login(User user)
{
return(gamestakDb.Use(async conn =>
{
var query = @$ "
select *
from {DbTables.Users}
where Username = @User and Password = @Pass
";
var response = await conn.QueryAsync <User>(query, new
{
User = user.Username,
Pass = user.Password,
});
return await PopulateUserFields(response.FirstOrDefault());
}));
}
async Task <string> __create_order_no__(string order_uid)
{
var sql = @$ "update {table} set
`OrderNo` = CONCAT('OD', LPAD(`Id`, {this.OrderNoLength}, '0')),
`TFNo` = CONCAT('TF', LPAD(`Id`, {this.OrderNoLength}, '0'))
where UID=@uid";
using (var db = this._dbFactory.GetMetroAdDatabase())
{
var rows = await db.ExecuteAsync(sql, new { uid = order_uid });
rows.Should().BeGreaterThan(0);
sql = $"select `OrderNo` from {table} where UID=@uid limit 1";
var res = await db.ExecuteScalarAsync <string>(sql, new { uid = order_uid });
return(res);
}
}
static async Task <int> InsertIPtoDB(string servername, string database, string accessToken, string blockIP)
{
int rows = 0;
#if LOCALDEV
string connectionString = Environment.GetEnvironmentVariable("SQL_CONNECTION");
#else
string connectionString = $"Data Source={servername}; Initial Catalog={database};";
#endif
using (SqlConnection conn = new SqlConnection(connectionString))
{
#if !LOCALDEV
conn.AccessToken = accessToken;
#endif
conn.Open();
SqlCommand command = new SqlCommand(@$ "
IF EXISTS(select IP_ADDR from WAFBLOCKIP where IP_ADDR = '{blockIP}')
BEGIN
update WAFBLOCKIP SET TTL = 15 where IP_ADDR = '{blockIP}'
private static where GetWhere()
{
where wh = where.NONE;
if (downPos.X > mousePos.X && downPos.Y < mousePos.Y)
{
wh = where.BOTTOM_LEFT;
}
else if (downPos.X < mousePos.X && downPos.Y > mousePos.Y)
{
wh = where.TOP_RIGHT;
}
else if (downPos.X > mousePos.X && downPos.Y > mousePos.Y)
{
wh = where.TOP_LEFT;
}
else
{
wh = where.BOTTOM_RIGHT;
}
return(wh);
}
GetTableDefintions(this NpgsqlConnection connection, Settings settings)
{
return(connection.Read <(
string Schema,
string Table,
string Name,
int Ord,
string Default,
string IsNullable,
string DataType,
string TypeUdtName,
string IsIdentity,
string ConstraintType)>(@$ "
select
t.table_schema,
t.table_name,
c.column_name,
c.ordinal_position,
c.column_default,
c.is_nullable,
c.data_type,
regexp_replace(c.udt_name, '^[_]', '') as udt_name,
c.is_identity,
tc.constraint_type
from
information_schema.tables t
inner join information_schema.columns c
on t.table_name = c.table_name and t.table_schema = c.table_schema
left outer join information_schema.key_column_usage kcu
on t.table_name = kcu.table_name and t.table_schema = kcu.table_schema and c.column_name = kcu.column_name
left outer join information_schema.table_constraints tc
on t.table_name = tc.table_name and t.table_schema = tc.table_schema and tc.constraint_name = kcu.constraint_name
where
table_type = 'BASE TABLE'
and
( @schema is null or (t.table_schema similar to @schema) )
and ( {GetSchemaExpression(" t.table_schema ")} )
public object Put(int id, [FromBody] ProductsModel product)
{
int execute = dapper.Execute(
@$ "
update Products
set
productName = @productName,
supplierID = @supplierID,
categoryID = @categoryID,
quantityPerUnit = @quantityPerUnit,
unitPrice = @unitPrice,
unitsInStock = @unitsInStock,
unitsOnOrder = @unitsOnOrder,
reorderLevel = @reorderLevel,
discontinued = @discontinued
where
productID = @productID",
new
{
ProductID = id,
productName = product.ProductName,
supplierID = product.SupplierID,
categoryID = product.CategoryID,
quantityPerUnit = product.QuantityPerUnit,
unitPrice = product.UnitPrice,
unitsInStock = product.UnitsInStock,
unitsOnOrder = product.UnitsOnOrder,
reorderLevel = product.ReorderLevel,
discontinued = product.Discontinued,
});
return(new
{
status = "ok",
message = "成功",
data = execute,
});
}
private ListProcedureParamInfo GetParamInfos(IDbConnection conn, string procedureName)
{
var rr = conn.QueryProcedureParamInfo($select PARAMETER_NAME, PARAMETER_MODE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from information_schema.parameters where specific_name = '{procedureName}');
private async Task <Stream> GetNaturalGasBillsInternalAsync(SqlParams @params)
{
var bills = await _dbConnection.QueryAsync <Bill>(@$ "select
company.name as CompanyFullName,
company.short_name as CompanyShortName,
company.www as CompanySite,
company.email as CompanyEmail,
company.taxpayer_phone as CompanyPhone,
company.state_registry_code as CompanyStateRegistryCode,
company.address as CompanyAddress,
branch_offices.iban as BranchOfficeIban,
branch_offices.bank_full_name as BranchOfficeBankFullName,
ap.name as AccountingPointName,
concat(address.cityName, ' ', address.streetName, ' ', address.building, ' ', address.apt) as AccountingPointAddress,
concat(people.last_name, ' ', people.first_name, ' ', people.patronymic) as OwnerFullName,
periods.name as PeriodShortDate,
apdh.debt_value::decimal as AccountingPointDebtHistory,
payments.sumAmount::decimal as PaymentSumByPeriod,
inv.total_units::decimal as InvoiceTotalUnits,
inv.total_amount_due::decimal as InvoiceTotalAmountDue,
(jsonb_array_elements(inv.usage_t1 -> 'Calculations') -> 'PriceValue')::decimal as TariffRate,
ap.debt::decimal as AccountingPointDebt
from invoices inv
join accounting_points ap on ap.id = inv.accounting_point_id
join branch_offices on ap.branch_office_id = branch_offices.id
join company on branch_offices.company_id = company.id
join periods on branch_offices.current_period_id = periods.id
join
(
select addresses.id, cities.name as cityName, streets.name as streetName, building, apt
from addresses
join streets on addresses.street_id = streets.id
join cities on streets.city_id = cities.id
) as address on ap.address_id = address.id
join people on ap.owner_id = people.id
left join accounting_point_debt_history as apdh on ap.id = apdh.accounting_point_id and apdh.period_id=inv.period_id
left join LATERAL
(
select p.accounting_point_id, sum(p.amount) as sumAmount
from payments as p
where period_id = inv.period_id and status = 1 and p.accounting_point_id=inv.accounting_point_id
group by p.accounting_point_id
) payments on true
where ap.branch_office_id = 101 and inv.period_id = case when @periodId is null then inv.period_id else @periodId end
and inv.id = case when @id is null then inv.id else @id end", @params);
var dict = new Dictionary <string, IList>
{
{ "bill_gas", bills.ToList() }
};
var ms = ReportBuilderXLS.GenerateReport(dict, "Templates/bill_gas.xlsx");
//if (@params.PeriodId.HasValue)
//{
var counter = 1;
using var naturalGasBills = new XLWorkbook(ms);
// using var mergedBills = new XLWorkbook();
// mergedBills.AddWorksheet();
// var electricitySpace = 01;
foreach (var bill in bills)
{
//var naturalGasBill = naturalGasBills.Worksheet(1).Range((counter - 1) * 17 + 1, 1, counter * 17, 15);
//mergedBills.Worksheet(1).Cell((counter - 1) * 17 + 1, 1).Value = naturalGasBill;
//mergedBills.Worksheet(1).LastRowUsed().InsertRowsBelow(1);
//var baseUri = _recsBillLocations.Single(l => l.Prefix == bill.AccountingPointName.Substring(0, 2)).BaseUri;
//var response = await _httpClient.GetAsync($"{baseUri}rp.name='{HttpUtility.UrlEncode(bill.AccountingPointName)}'?lastOnly=False");
//using var electricityBill = new XLWorkbook(await response.Content.ReadAsStreamAsync());
//var electricity = electricityBill.Range("Bill");
//var spaceIncriment = 24;
//if (electricity.LastRow().RowNumber() < 2)
//{
// electricity = electricityBill.Range("Billzone");
// spaceIncriment = 31;
//}
//if (electricity.LastRow().RowNumber() > 10)
// naturalGasBills.Worksheet(1).Cell((counter * 19) + electricitySpace, 1).Value = electricity;
//else
// spaceIncriment = 0;
//electricitySpace += spaceIncriment;
//naturalGasBills.Worksheet(1).PageSetup.AddHorizontalPageBreak(naturalGasBills.Worksheet(1).LastRowUsed().RowNumber());
var ws = naturalGasBills.Worksheet(1);
ws.AddPicture(@"Templates/n_gas.png")
.MoveTo(ws.Cell($"A{(counter - 1) * 19 + 7}"))
.Scale(0.66); // optional: resize picture
if (counter != 0 && counter % 4 == 0)
{
ws.PageSetup.AddHorizontalPageBreak(counter * 19);
}
counter++;
}
// var s = new MemoryStream();
// mergedBills.SaveAs(s);
// s.Position = 0;
// return s;
//}
naturalGasBills.SaveAs(ms);
ms.Position = 0;
return(ms);
}
// Add each predicate to the where and parameter lists var(where, param) = QueryF.GetWhereAndParameters(this, predicates, false);
public static void Take()
{
Form SelectFrm = new DummyForm();
SelectFrm.Cursor = Cursors.Cross;
/// Make sure that the form will use the *entire* screen bounds, or else it might shrink due to window tiling.
SelectFrm.MaximumSize = new Size(Screen.PrimaryScreen.Bounds.Width, Screen.PrimaryScreen.Bounds.Height);
SelectFrm.WindowState = FormWindowState.Maximized;
SelectFrm.FormBorderStyle = FormBorderStyle.None;
SelectFrm.ShowInTaskbar = false;
SelectFrm.Opacity = Settings.Frozen ? 1.0 : 0.5;
SelectFrm.BackColor = Color.White;
SelectFrm.TopMost = true;
Bitmap bm = null;
if (Settings.Frozen)
{
bm = new Bitmap(Screen.PrimaryScreen.Bounds.Width, Screen.PrimaryScreen.Bounds.Height, PixelFormat.Format32bppArgb);
Graphics snapShot = Graphics.FromImage(bm);
snapShot.CopyFromScreen(0, 0,
Screen.PrimaryScreen.Bounds.X,
Screen.PrimaryScreen.Bounds.Y,
Screen.PrimaryScreen.Bounds.Size,
CopyPixelOperation.SourceCopy);
bm = bm.Clone(new Rectangle(0, 0, Screen.PrimaryScreen.Bounds.Width, Screen.PrimaryScreen.Bounds.Height), PixelFormat.Format32bppArgb);
SelectFrm.BackgroundImage = bm;
}
SelectFrm.Paint += (sender, e) =>
{
mousePos = (down) ? Cursor.Position : upPos;
where wh = GetWhere();
selectedRect = new Rectangle(Math.Min(downPos.X, mousePos.X),
Math.Min(downPos.Y, mousePos.Y),
Math.Abs(downPos.X - mousePos.X),
Math.Abs(downPos.Y - mousePos.Y));
e.Graphics.FillRectangle(new SolidBrush(Color.FromArgb(150, Color.Gray)), selectedRect);
e.Graphics.DrawRectangle(new Pen(Color.FromArgb(0x00, 0x00, 0x00), 1), selectedRect);
if (Settings.Frozen)
{
e.Graphics.FillRectangle(new SolidBrush(Color.FromArgb(150, Color.Gray)),
new Rectangle(0, 0, Screen.PrimaryScreen.Bounds.Width, Screen.PrimaryScreen.Bounds.Height)
);
}
String info = selectedRect.Width + "\n" + selectedRect.Height;
SizeF sizef = e.Graphics.MeasureString(info, new Font("Arial", 8));
PointF point = PointFromWhere(wh, sizef);
e.Graphics.DrawString(info, new Font("Arial", 8), new SolidBrush(Color.Black),
point);
};
SelectFrm.MouseDown += (sender, e) =>
{
SelectFrm.Invalidate();
down = true;
downPos = new Point(e.X, e.Y);
};
SelectFrm.MouseMove += (sender, e) =>
{
if (down)
{
SelectFrm.Invalidate();
}
};
SelectFrm.MouseUp += (sender, e) =>
{
down = false;
upPos = new Point(e.X, e.Y);
if (e.Button == MouseButtons.Right)
{
SelectFrm.DialogResult = DialogResult.Cancel;
}
else if (e.Button == MouseButtons.Left)
{
SelectFrm.DialogResult = DialogResult.OK;
}
};
if (SelectFrm.ShowDialog() == DialogResult.OK)
{
try
{
Bitmap bitmap = new Bitmap(Screen.PrimaryScreen.Bounds.Width,
Screen.PrimaryScreen.Bounds.Height,
PixelFormat.Format32bppArgb);
Graphics snapShot = Graphics.FromImage(bitmap);
snapShot.CopyFromScreen(0, 0,
Screen.PrimaryScreen.Bounds.X,
Screen.PrimaryScreen.Bounds.Y,
Screen.PrimaryScreen.Bounds.Size,
CopyPixelOperation.SourceCopy);
if (bm != null)
{
bitmap = bm.Clone(selectedRect, PixelFormat.Format32bppArgb);
}
else
{
bitmap = bitmap.Clone(selectedRect, PixelFormat.Format32bppArgb);
}
Upload(bitmap);
bitmap.Dispose();
snapShot.Dispose();
SelectFrm.Dispose();
}
catch (Exception ex)
{
LogError(ex);
MessageBox.Show(string.Format("An error occured!\n"
+ "Please send the contents of your error.log to the developer."), "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
if (!SelectFrm.IsDisposed)
{
SelectFrm.Dispose();
}
CleanUp();
}
public Task <bool> CreateVendorOrdersTable(string tblName)
{
string sql = @$ "CREATE SCHEMA IF NOT EXISTS VendorOrder; CREATE TABLE VendorOrder.Vendor{tblName}Orders (
Id varchar(50) NOT NULL CONSTRAINT PK_Vendor{tblName}Orders PRIMARY KEY,
IP varchar(50) NULL,
IsActive bool NOT NULL,
CreatedDateTime timestamptz NOT NULL,
UpdatedDateTime timestamptz NOT NULL,
ModifyBy varchar(50) NULL,
OrderStatusId int NOT NULL,
ItemCount int NOT NULL,
DeliveryCost decimal(10,2) NOT NULL,
TotalAmount decimal(10,2) NOT NULL,
Discount decimal(10,2) NOT NULL,
PromoCode varchar(50) NULL,
DeliveryStatus varchar(50) NULL,
DeliveryDate timestamptz NULL,
InvoiceNo varchar(50) NULL,
PaymentStatus varchar(50) NULL,
UserId varchar(50) NULL,
UserAddressDetails varchar(1000) NOT NULL,
ShopAddressDetails varchar(1000) NOT NULL,
OrdersDetails text NOT NULL,
CurrencyType varchar(10),
ShopId varchar(50) NULL,
CONSTRAINT FK_Vendor{tblName}Orders_Users_UserId FOREIGN KEY (UserId) REFERENCES AspNetUsers (Id) ON DELETE CASCADE
);
CREATE INDEX index_Vendor{tblName}Orders_PriceHiddenByShopper ON VendorOrder.Vendor{tblName}Orders (OrderStatusId) where OrderStatusId={(int)EnumOrderStatus.PriceHiddenByShopper};
CREATE INDEX index_Vendor{tblName}Orders_UnderPreparing ON VendorOrder.Vendor{tblName}Orders (OrderStatusId) where OrderStatusId={(int)EnumOrderStatus.UnderPreparing};
CREATE INDEX index_Vendor{tblName}Orders_ReadyForDelivery ON VendorOrder.Vendor{tblName}Orders (OrderStatusId) where OrderStatusId={(int)EnumOrderStatus.ReadyForDelivery};
CREATE INDEX index_Vendor{tblName}Orders_Cancel ON VendorOrder.Vendor{tblName}Orders (OrderStatusId) where OrderStatusId={(int)EnumOrderStatus.Cancel};
CREATE INDEX index_Vendor{tblName}Orders_Delivered ON VendorOrder.Vendor{tblName}Orders (OrderStatusId) where OrderStatusId={(int)EnumOrderStatus.Delivered};
CREATE INDEX index_Vendor{tblName}Orders_CreatedDateTime ON VendorOrder.Vendor{tblName}Orders using BRIN (CreatedDateTime);
CREATE INDEX index_Vendor{tblName}Orders_ModifyBy ON VendorOrder.Vendor{tblName}Orders using BTREE (ModifyBy);
" ;
using (var command = _context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = sql;
_context.Database.OpenConnection();
command.ExecuteNonQuery();
}
return(Task.FromResult(true));
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
int anoMenor = 0;
int anoMaior = 0;
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
DataTable dtable = new DataTable();
MySqlCommand insert = new MySqlCommand("", conn);
insert.CommandTimeout = 86400;
try
{
MySqlCommand create = new MySqlCommand($@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_acumulado_siga;
DELETE FROM acumulado;
DELETE FROM acumuladoagrupado;
DELETE FROM turmadiario;
CREATE TABLE `mig_acumulado_siga` (
`CODACUMULADO` INT(11) NOT NULL AUTO_INCREMENT,
`MATRICULA` varchar(100) NOT NULL,
`CODMATERIA` varchar(100) NOT NULL,
`ANO` varchar(100) NOT NULL,
`CODSERIE` varchar(100) NOT NULL,
`CODTURMA` varchar(100) NOT NULL,
`MEDIA1` varchar(100) NULL DEFAULT NULL,
`FALTAS1` varchar(100) NULL DEFAULT NULL,
`MEDIA2` varchar(100) NULL DEFAULT NULL,
`FALTAS2` varchar(100) NULL DEFAULT NULL,
`MEDIA3` varchar(100) NULL DEFAULT NULL,
`FALTAS3` varchar(100) NULL DEFAULT NULL,
`MEDIA4` varchar(100) NULL DEFAULT NULL,
`FALTAS4` varchar(100) NULL DEFAULT NULL,
`RECSEMESTRE1` varchar(100) NULL DEFAULT NULL,
`RECSEMESTRE2` varchar(100) NULL DEFAULT NULL,
`RECFINAL` varchar(100) NULL DEFAULT NULL,
`RECFINAL2` varchar(100) NULL DEFAULT NULL,
`RECFINAL3` varchar(100) NULL DEFAULT NULL,
`alterado` CHAR(1) NULL DEFAULT NULL,
`MEDSEM1` varchar(100) NULL DEFAULT NULL,
`MEDSEM2` varchar(100) NULL DEFAULT NULL,
`RESULTFINAL` varchar(100) NULL DEFAULT NULL,
`TOTFALTAS` varchar(100) NULL DEFAULT NULL,
`SITUACAO` CHAR(1) NULL DEFAULT NULL,
`codprofturma` varchar(100) NULL DEFAULT NULL,
`rec1` varchar(100) NULL DEFAULT NULL,
`rec2` varchar(100) NULL DEFAULT NULL,
`rec3` varchar(100) NULL DEFAULT NULL,
`rec4` varchar(100) NULL DEFAULT NULL,
`soma1` varchar(100) NULL DEFAULT NULL,
`soma2` varchar(100) NULL DEFAULT NULL,
`soma3` varchar(100) NULL DEFAULT NULL,
`soma4` varchar(100) NULL DEFAULT NULL,
`somasemestre1` varchar(100) NULL DEFAULT NULL,
`somasemestre2` varchar(100) NULL DEFAULT NULL,
`CODPROFESSOR` varchar(100) NULL DEFAULT NULL,
`usuario` varchar(100) NULL DEFAULT NULL,
`codcalculoacumulado` varchar(100) NULL DEFAULT NULL,
`dtvalidacaocalculo` varchar(100) NULL DEFAULT NULL,
`somaetapas` varchar(100) NULL DEFAULT NULL,
`mig_conceito1` VARCHAR(5) NULL DEFAULT NULL,
`mig_conceito2` VARCHAR(5) NULL DEFAULT NULL,
`mig_conceito3` VARCHAR(5) NULL DEFAULT NULL,
`mig_conceito4` VARCHAR(5) NULL DEFAULT NULL,
`mig_conceitofinal` VARCHAR(5) NULL DEFAULT NULL,
`mig_pontosaprovconselho` varchar(100) NULL DEFAULT NULL,
PRIMARY KEY (`CODACUMULADO`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;", conn);
create.ExecuteNonQuery();
ResetaValorAcumulado.Resetar(out anoMenor, out anoMaior, FbConn);
while (anoMenor <= anoMaior)
{
dtable.Clear();
FbCommand select = new FbCommand($@"select
-- d.descricao,
m.codigo as MATRICULA,
a.disciplina as CODMATERIA,
a.anoletivo as ANO,
(m.unidade || m.curso || m.serie) as CODSERIE,
tt.codturma as CODTURMA,
iif(a.resultbim1 in ('A','B','C','D','E','DISP'),NULL, a.resultbim1) as MEDIA1,
a.faltabim1 as FALTAS1,
iif(a.resultbim2 in ('A','B','C','D','E','DISP'),NULL, a.resultbim2) as MEDIA2,
a.faltabim2 as FALTAS2,
iif(a.resultbim3 in ('A','B','C','D','E','DISP'),NULL, a.resultbim3) as MEDIA3,
a.faltabim3 as FALTAS3,
iif(a.resultbim4 in ('A','B','C','D','E','DISP'),NULL, a.resultbim4) as MEDIA4,
a.faltabim4 as FALTAS4,
iif(a.notarecup in ('A','B','C','D','E','DISP'),NULL, a.notarecup) as RECFINAL,
iif(a.mediafinal in ('A','B','C','D','E','DISP'),NULL, a.mediafinal) as RESULTFINAL,
(coalesce(a.faltabim1,0) + coalesce(a.faltabim2,0) + coalesce(a.faltabim3,0) + coalesce(a.faltabim4,0)) as TOTFALTAS,
iif(a.recupbim1 in ('A','B','C','D','E','DISP'),NULL, a.recupbim1) as rec1,
iif(a.recupbim2 in ('A','B','C','D','E','DISP'),NULL, a.recupbim2) as rec2,
iif(a.recupbim3 in ('A','B','C','D','E','DISP'),NULL, a.recupbim3) as rec3,
iif(a.recupbim4 in ('A','B','C','D','E','DISP'),NULL, a.recupbim4) as rec4,
iif(a.mediabim1 in ('A','B','C','D','E','DISP'),NULL, a.mediabim1) as soma1,
iif(a.mediabim2 in ('A','B','C','D','E','DISP'),NULL, a.mediabim2) as soma2,
iif(a.mediabim3 in ('A','B','C','D','E','DISP'),NULL, a.mediabim3) as soma3,
iif(a.mediabim4 in ('A','B','C','D','E','DISP'),NULL, a.mediabim4) as soma4,
iif(a.mediabim in ('A','B','C','D','E','DISP'),NULL, a.mediabim) as somaetapas,
iif(a.resultbim1 in ('A','B','C','D','E'),a.resultbim1,NULL ) as mig_conceito1,
iif(a.resultbim2 in ('A','B','C','D','E'),a.resultbim2,NULL ) as mig_conceito2,
iif(a.resultbim3 in ('A','B','C','D','E'),a.resultbim3,NULL ) as mig_conceito3,
iif(a.resultbim4 in ('A','B','C','D','E'),a.resultbim4,NULL ) as mig_conceito4,
iif(a.mediafinal in ('A','B','C','D','E'),a.mediafinal,NULL ) as mig_conceitofinal,
iif(a.notaconse in ('A','B','C','D','E','DISP'),NULL, a.notaconse) as mig_pontosaprovconselho -- ,
-- a.*
from signotfa a
join sigaluno m on (a.unidade = m.unidade and a.anoletivo = m.anoletivo and a.periodo = m.periodo and a.curso = m.curso and a.serie = m.serie and a.classe = m.classe and a.turno = m.turno and a.chamada = m.chamada )
join sigclass t on (a.unidade = t.unidade and a.anoletivo = t.anoletivo and a.periodo = t.periodo and a.curso = t.curso and a.serie = t.serie and a.classe = t.classe and a.turno = t.turno)
join turma_tella tt on tt.dscturma = (COALESCE(t.classe,'X') || COALESCE(t.turno,'Y') || COALESCE(t.periodo,'Z'))
join sigdisci d on (d.codigo = a.disciplina)
where a.anoletivo = {anoMenor} and a.resultbim1 is null", conn2);
FbDataAdapter adapter = new FbDataAdapter(select);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append($@"INSERT INTO mig_acumulado_siga (MATRICULA, CODMATERIA, ANO, CODSERIE, CODTURMA, MEDIA1, FALTAS1, MEDIA2, FALTAS2, MEDIA3, FALTAS3, MEDIA4, FALTAS4, RECFINAL, RESULTFINAL, TOTFALTAS, rec1, rec2, rec3, rec4, soma1, soma2, soma3, soma4, somaetapas, mig_conceito1, mig_conceito2, mig_conceito3, mig_conceito4, mig_conceitofinal, mig_pontosaprovconselho ) VALUES ");
try
{
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["MATRICULA"]}' , '{dtable.Rows[i]["CODMATERIA"]}' , '{dtable.Rows[i]["ANO"]}' , '{dtable.Rows[i]["CODSERIE"]}' , '{dtable.Rows[i]["CODTURMA"]}' , '{dtable.Rows[i]["MEDIA1"]}' , '{dtable.Rows[i]["FALTAS1"]}' , '{dtable.Rows[i]["MEDIA2"]}' , '{dtable.Rows[i]["FALTAS2"]}' ,'{dtable.Rows[i]["MEDIA3"]}' , '{dtable.Rows[i]["FALTAS3"]}' , '{dtable.Rows[i]["MEDIA4"]}' , '{dtable.Rows[i]["FALTAS4"]}' , '{dtable.Rows[i]["RECFINAL"]}' , '{dtable.Rows[i]["RESULTFINAL"]}' , '{dtable.Rows[i]["TOTFALTAS"]}' , '{dtable.Rows[i]["rec1"]}' , '{dtable.Rows[i]["rec2"]}' , '{dtable.Rows[i]["rec3"]}' , '{dtable.Rows[i]["rec4"]}' , '{dtable.Rows[i]["soma1"]}' , '{dtable.Rows[i]["soma2"]}' , '{dtable.Rows[i]["soma3"]}' , '{dtable.Rows[i]["soma4"]}' , '{dtable.Rows[i]["somaetapas"]}' , '{dtable.Rows[i]["mig_conceito1"]}' , '{dtable.Rows[i]["mig_conceito2"]}' , '{dtable.Rows[i]["mig_conceito3"]}' , '{dtable.Rows[i]["mig_conceito4"]}' , '{dtable.Rows[i]["mig_conceitofinal"]}' , '{dtable.Rows[i]["mig_pontosaprovconselho"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
insert = new MySqlCommand(queryBuilder.ToString(), conn);
insert.ExecuteNonQuery();
}
catch (Exception err)
{
if (err.Message == "Packets larger than max_allowed_packet are not allowed.")
{
for (int i = 0; i < dtable.Rows.Count; i++)
{
insert.CommandText = $@"INSERT INTO mig_acumulado_siga (MATRICULA, CODMATERIA, ANO, CODSERIE, CODTURMA, MEDIA1, FALTAS1, MEDIA2, FALTAS2, MEDIA3, FALTAS3, MEDIA4, FALTAS4, RECFINAL, RESULTFINAL, TOTFALTAS, rec1, rec2, rec3, rec4, soma1, soma2, soma3, soma4, somaetapas, mig_conceito1, mig_conceito2, mig_conceito3, mig_conceito4, mig_conceitofinal, mig_pontosaprovconselho ) VALUES
('{dtable.Rows[i]["MATRICULA"]}' , '{dtable.Rows[i]["CODMATERIA"]}' , '{dtable.Rows[i]["ANO"]}' , '{dtable.Rows[i]["CODSERIE"]}' , '{dtable.Rows[i]["CODTURMA"]}' , '{dtable.Rows[i]["MEDIA1"]}' , '{dtable.Rows[i]["FALTAS1"]}' , '{dtable.Rows[i]["MEDIA2"]}' , '{dtable.Rows[i]["FALTAS2"]}' ,'{dtable.Rows[i]["MEDIA3"]}' , '{dtable.Rows[i]["FALTAS3"]}' , '{dtable.Rows[i]["MEDIA4"]}' , '{dtable.Rows[i]["FALTAS4"]}' , '{dtable.Rows[i]["RECFINAL"]}' , '{dtable.Rows[i]["RESULTFINAL"]}' , '{dtable.Rows[i]["TOTFALTAS"]}' , '{dtable.Rows[i]["rec1"]}' , '{dtable.Rows[i]["rec2"]}' , '{dtable.Rows[i]["rec3"]}' , '{dtable.Rows[i]["rec4"]}' , '{dtable.Rows[i]["soma1"]}' , '{dtable.Rows[i]["soma2"]}' , '{dtable.Rows[i]["soma3"]}' , '{dtable.Rows[i]["soma4"]}' , '{dtable.Rows[i]["somaetapas"]}' , '{dtable.Rows[i]["mig_conceito1"]}' , '{dtable.Rows[i]["mig_conceito2"]}' , '{dtable.Rows[i]["mig_conceito3"]}' , '{dtable.Rows[i]["mig_conceito4"]}' , '{dtable.Rows[i]["mig_conceitofinal"]}' , '{dtable.Rows[i]["mig_pontosaprovconselho"]}');";
insert.ExecuteNonQuery();
}
}
else
{
MessageBox.Show(err.Message);
conn2.Close();
conn.Close();
}
}
anoMenor++;
}
MySqlCommand update = new MySqlCommand(@$ "
UPDATE mig_acumulado_siga set matricula = NULL where matricula = '';
update mig_acumulado_siga set codmateria = null where codmateria = '';
update mig_acumulado_siga set ano = null where ano = '';
update mig_acumulado_siga set codserie = null where codserie = '';
update mig_acumulado_siga set codturma = null where codturma = '';
UPDATE mig_acumulado_siga set media1 = NULL where media1 = '';
UPDATE mig_acumulado_siga set faltas1 = NULL where faltas1 = '';
update mig_acumulado_siga set media2 = null where media2 = '';
UPDATE mig_acumulado_siga set faltas2 = NULL where faltas2 = '';
update mig_acumulado_siga set media3 = null where media3 = '';
update mig_acumulado_siga set faltas3 = null where faltas3 = '';
update mig_acumulado_siga set media4 = null where media4 = '';
UPDATE mig_acumulado_siga set faltas4 = NULL where faltas4 = '';
UPDATE mig_acumulado_siga set recfinal = NULL where recfinal = '';
update mig_acumulado_siga set resultfinal = null where resultfinal = '';
update mig_acumulado_siga set totfaltas = null where totfaltas = '';
update mig_acumulado_siga set rec1 = null where rec1 = '';
update mig_acumulado_siga set rec2 = null where rec2 = '';
UPDATE mig_acumulado_siga set rec3 = NULL where rec3 = '';
UPDATE mig_acumulado_siga set rec4 = NULL where rec4 = '';
update mig_acumulado_siga set soma1 = null where soma1 = '';
update mig_acumulado_siga set soma2 = null where soma2 = '';
update mig_acumulado_siga set soma3 = null where soma3 = '';
update mig_acumulado_siga set soma4 = null where soma4 = '';
UPDATE mig_acumulado_siga set somaetapas = NULL where somaetapas = '';
UPDATE mig_acumulado_siga set mig_conceito1 = NULL where mig_conceito1 = '';
update mig_acumulado_siga set mig_conceito2 = null where mig_conceito2 = '';
update mig_acumulado_siga set mig_conceito3 = null where mig_conceito3 = '';
update mig_acumulado_siga set mig_conceito4 = null where mig_conceito4 = '';
update mig_acumulado_siga set mig_conceitofinal = null where mig_conceitofinal = '';
update mig_acumulado_siga set mig_pontosaprovconselho = null where mig_pontosaprovconselho = '' or mig_pontosaprovconselho REGEXP '^-?[A-Z]+$';", conn);
public IActionResult GetPerscription(int id)
{
using (SqlConnection con = new SqlConnection("Data Source = db-mssql; Initial Catalog=s18830; Integrated Security=True"))
com.Connection = con;
using (SqlCommand com = new SqlCommand())
{
try
{
var list = new List<Prescriptions>();
var lista = new List<PrescriptionsMedicament>();
com.Parameters.AddWithValue("IdPrescription", id);
com.CommandText = "select Name Type,Date ,DueDate ,
Description ,Dose , IdPatient,IdDoctor from Prescription_Medicament
JOIN Prescription on Prescription.IdPrescription = Prescription_Medicament.IdPrescription
JOIN Medicament on Medicament.IdMedicament = Prescription_Medicament.IdMedicament where IdPrescription =@id";
con.Open();
SqlDataReader dr = com.ExecuteReader();
while (dr.Read()){
var recepta = new Prescriptions();
recepta.IdPerscription = (int)dr["IdPrescription"];
recepta.Date = DateTime.Parse(dr["Date"].ToString());
recepta.DueTime = DateTime.Parse(dr["DueDate"].ToString());
recepta.IdPatient = (int)dr["IdPatient"];
recepta.IdDoctor = (int)dr["IdDoctor"];
var permed = new PrescriptionsMedicament();
var listaMed = new List<Medicament>();
permed.IdPerscription = (int)dr["IdPrescription"]; ;
permed.IdMedicament = (int)dr["IdMedicament"];
listaMed.Dose = (int)dr["Dose"];
listaMed.Details = (dr["Details"].ToString());
lista.Add(permed);
list.Add(recepta);
}
return Ok(list + "\n" + lista);
}
catch (SqlException sql)
{
return BadRequest("Wysłano nieprawidłowe zapytanie");
}
}
}
=> await _repository.QueryPageAsync(where, pageIndex, pageSize);
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
DataTable dtable = new DataTable();
FbCommand MySelect = new FbCommand(@"select c.banco as codbanco,
c.nome as dscconta,
c.conta as numconta,
c.carteira as carteira,
c.sequencia as sequencialremessa,
c.agencia as agencia
from sigbccta c;", conn2);
//Crie um data adapter para trabalhar com datatables.
//Datatables são mais fáceis de manipular que utilizar o método Read()
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_conta_siga;
DELETE FROM conta;
CREATE TABLE mig_conta_siga (
CODBANCO VARCHAR(4),
DSCCONTA VARCHAR(42),
NUMCONTA VARCHAR(10),
CARTEIRA VARCHAR(3),
SEQUENCIALREMESSA varchar(100),
AGENCIA VARCHAR(5));" +
"INSERT INTO mig_conta_siga (codbanco,dscconta,numconta,carteira, sequencialremessa,agencia) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["codbanco"]}' , '{dtable.Rows[i]["dscconta"]}' , '{dtable.Rows[i]["numconta"]}' , '{dtable.Rows[i]["carteira"]}' , '{dtable.Rows[i]["sequencialremessa"]}' , '{dtable.Rows[i]["agencia"]}'), ");
}
//Remove a última vírgula da consulta, para evitar erros de sintaxe.
queryBuilder.Remove(queryBuilder.Length - 2, 2);
//O segredo da perfomace está aqui: Você somente executa a operação após construir toda a consulta.
//Antes você estava executando uma chamada no banco de dados a cada iteração do while. E isto é um pecado, em relação a perfomace =D
//var s = queryBuilder.ToString();
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
MySqlCommand update = new MySqlCommand(@$ "
UPDATE mig_conta_siga SET sequencialremessa = NULL where sequencialremessa = '';
UPDATE mig_conta_siga SET carteira = NULL where carteira = '';", conn);
update.ExecuteNonQuery();
MySqlCommand insert = new MySqlCommand(@$ "SET FOREIGN_KEY_CHECKS = 0; INSERT INTO conta (codbanco,dscconta,numconta,carteira,sequencialremessa,agencia)
(SELECT right(if(codbanco = '0356','033',codbanco),3) as codbanco,dscconta,numconta,carteira,sequencialremessa,agencia FROM mig_conta_siga);
INSERT INTO conta (dscconta) VALUES ('CONTA MIGRACAO');", conn); //usado no ifnull do contasreceber > insert pagamentoforma
insert.ExecuteNonQuery();
//para configurar o codempresa, é preciso ver com o cliente a qual EMPRESA a CONTA pertence
MessageBox.Show("Importação concluída com sucesso, agora, configure o codempresa da tabela conta");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from conta;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
private static PointF PointFromWhere(where wh, SizeF sizef)
{
PointF point = new PointF();
switch (wh)
{
case where.NONE:
point = new Point(-10, -10);
break;
case where.TOP_LEFT:
point = new PointF((float)(mousePos.X + 2), (float)(mousePos.Y + 2));
break;
case where.TOP_RIGHT:
point = new PointF((float)(mousePos.X - sizef.Width - 2), (float)(mousePos.Y + 2));
break;
case where.BOTTOM_LEFT:
point = new PointF((float)(mousePos.X + 2), (float)(mousePos.Y - sizef.Height - 2));
break;
case where.BOTTOM_RIGHT:
point = new PointF((float)(mousePos.X - sizef.Width - 2), (float)(mousePos.Y - sizef.Height - 2));
break;
}
return point;
}
public void UpdateFuncionario(Funcionario funcionario)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
string comandoSQL = "Update Funcionarios set Nome = @Nome, Cidade = @Cidade, Departamento =
@Departamento, Sexo = @Sexo where FuncionarioId = @FuncionarioId";
SqlCommand cmd = new SqlCommand(comandoSQL, con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@FuncionarioId", funcionario.FuncionarioId);
cmd.Parameters.AddWithValue("@Nome", funcionario.Nome);
cmd.Parameters.AddWithValue("@Cidade", funcionario.Cidade);
cmd.Parameters.AddWithValue("@Departamento", funcionario.Departamento);
cmd.Parameters.AddWithValue("@Sexo", funcionario.Sexo);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
