public static void FillOrder(string filename, ulong[] ids)
{
var table = new DbfTable();
table.Columns(
Column.Numeric("NUMZ", 8),
Column.Date("DATEZ"),
Column.Char("CODEPST", 12),
Column.Numeric("PAYID", 2),
Column.Date("DATE"),
Column.Char("PODR", 40),
Column.Numeric("QNT", 8),
Column.Numeric("PRICE", 9, 2),
Column.Char("PODRCD", 12),
Column.Char("NAME", 80)); // расширение протокола
table.Row(
Value.For("NUMZ", 2001),
Value.For("DATEZ", DateTime.Now),
Value.For("CODEPST", ids[0]),
Value.For("PAYID", 1), // по колонке PRICE1 прайслиста
Value.For("DATE", DateTime.Now),
Value.For("PODR", "аптека"),
Value.For("QNT", 1),
Value.For("PRICE", 39.94),
Value.For("PODRCD", "1"),
Value.For("NAME", "АНАЛЬГИН АМП. 50% 2МЛ N10 РОССИЯ")
);
Dbf2.SaveAsDbf4(table.ToDataTable(), filename);
}
public void Import_order_dbf()
{
TestClient client;
var price = SetupSupplierDeliveryId(out client);
var root = Directory.CreateDirectory($"tmp/{client.Users[0].Id}/orders/");
var table = FillOrder(price.Core.Select(x => (object)x.Id).Take(2).ToArray());
using (var file = new StreamWriter(File.Create(Path.Combine(root.FullName, "order.dbf")), Encoding.GetEncoding(866)))
Dbf2.SaveAsDbf4(table, file);
Program.ProcessUser(config, client.Users[0].Id, ProtocolType.Dbf);
var orders = session.Query <TestOrder>().Where(x => x.Client.Id == client.Id).ToList();
Assert.AreEqual(1, orders.Count);
}
public void Parse_decimal_count()
{
TestClient client;
var price = SetupSupplierDeliveryId(out client);
var root = Directory.CreateDirectory($"tmp/{client.Users[0].Id}/orders/");
var table = new DbfTable();
table.Columns(
Column.Numeric("NUMZ", 8),
Column.Date("DATEZ"),
Column.Char("CODEPST", 12),
Column.Numeric("PAYID", 2),
Column.Date("DATE"),
Column.Char("PODR", 40),
Column.Numeric("QNT", 19, 5),
Column.Numeric("PRICE", 9, 2),
Column.Char("PODRCD", 12),
Column.Char("NAME", 80),
Column.Numeric("XCODE", 20)); // расширение протокола
table.Row(
Value.For("NUMZ", 2001),
Value.For("DATEZ", DateTime.Now),
Value.For("CODEPST", "135"),
Value.For("PAYID", 1), // по колонке PRICE1 прайслиста
Value.For("DATE", DateTime.Now),
Value.For("PODR", "аптека"),
Value.For("QNT", 1.0m),
Value.For("PRICE", 39.94),
Value.For("PODRCD", "1"),
Value.For("NAME", "АНАЛЬГИН АМП. 50% 2МЛ N10 РОССИЯ"),
Value.For("XCODE", price.Core[0].Id)
);
Dbf2.SaveAsDbf4(table.ToDataTable(), Path.Combine(root.FullName, "order.dbf"));
Program.ProcessUser(config, client.Users[0].Id, ProtocolType.Dbf);
var orders = session.Query <TestOrder>().Where(x => x.Client.Id == client.Id).ToList();
Assert.AreEqual(1, orders.Count);
}
protected override void DataTableToDbf(DataTable dtExport, string fileName)
{
if (SplitByPrice)
{
var groups = data.AsEnumerable().GroupBy(r => r["RlSpplrId"]);
foreach (var price in groups)
{
var table = price.CopyToDataTable();
var filename = price.Key + ".dbf";
filename = Path.Combine(Path.GetDirectoryName(fileName), filename);
MailMetaOverride[filename] = Session.Load <Supplier>(Convert.ToUInt32(price.Key)).Name;
using (var writer = new StreamWriter(filename, false, Encoding.GetEncoding(866)))
Dbf2.SaveAsDbf4(table, writer);
}
}
else
{
using (var writer = new StreamWriter(fileName, false, Encoding.GetEncoding(866)))
Dbf2.SaveAsDbf4(dtExport, writer);
}
}
public static void ExportWaybills(DirectoryInfo root, ISession session, uint userId, ProtocolType ftpFileType)
{
var sendedWaybills = session.CreateSQLQuery(@"
select dh.Id, ds.Id as SendLogId
from Logs.DocumentSendLogs ds
join Logs.Document_logs d on d.RowId = ds.DocumentId
join Documents.DocumentHeaders dh on dh.DownloadId = d.RowId
where ds.UserId = :userId
and ds.Committed in (0, 2)
order by d.LogTime desc
limit 400;")
.SetParameter("userId", userId)
.List <object[]>();
foreach (var item in sendedWaybills)
{
var id = Convert.ToUInt32(item[0]);
var doc = session.Load <Document>(id);
if (ftpFileType == ProtocolType.Xml)
{
var name = Path.Combine(root.FullName, id + ".xml");
Protocols.Xml.SaveInFile(name, t => Protocols.Xml.Waybill(session, t, doc));
}
else if (ftpFileType == ProtocolType.Dbf)
{
var supplierDeliveryId = Program.GetSupplierDeliveryId(session, doc);
var name = Path.Combine(root.FullName, $"{id}_{supplierDeliveryId}.dbf");
Dbf2.SaveAsDbf4(Protocols.Dbf.Waybll(session, doc), name);
}
else if (ftpFileType == ProtocolType.DbfAsna)
{
var name = Path.Combine(root.FullName, id + ".dbf");
Dbf2.SaveAsDbf4(Protocols.DbfAsna.Waybill(session, doc), name);
}
var sendLog = session.Load <DocumentSendLog>(Convert.ToUInt32(item[1]));
sendLog.Commit();
}
}
public static void ExportPrices(ISession session, uint userId, DirectoryInfo root, ProtocolType ftpFileType)
{
var offers = QueryOffers(session, userId);
foreach (var group in offers.GroupBy(o => o.PriceList))
{
var activePrice = @group.Key;
if (ftpFileType == ProtocolType.Xml)
{
var name = Path.Combine(root.FullName, $"{activePrice.Id.Price.PriceCode}_{activePrice.Id.RegionCode}.xml");
Protocols.Xml.SaveInFile(name, t => Protocols.Xml.Price(t, activePrice, group));
}
else if (ftpFileType == ProtocolType.Dbf)
{
var name = Path.Combine(root.FullName, $"{activePrice.Id.Price.PriceCode}_{activePrice.Id.RegionCode}.dbf");
Dbf2.SaveAsDbf4(Protocols.Dbf.Price(activePrice, @group), name);
}
else if (ftpFileType == ProtocolType.DbfAsna)
{
var name = Path.Combine(root.FullName, $"{activePrice.Id.Price.PriceCode}_{activePrice.Id.RegionCode}.dbf");
Dbf2.SaveAsDbf4(Protocols.DbfAsna.Price(@group), name);
}
}
}
public static void SaveUniversalV2(Document document, string file)
{
var table = new DataTable();
table.Columns.AddRange(new[] {
new DataColumn("postid_af", typeof(int)),
new DataColumn("PostNameAF")
{
MaxLength = 255
},
new DataColumn("apt_af", typeof(int)),
new DataColumn("aname_af")
{
MaxLength = 255
},
new DataColumn("ttn")
{
MaxLength = 50
},
new DataColumn("ttn_date", typeof(DateTime)),
new DataColumn("id_artis", typeof(int)),
new DataColumn("idproducer", typeof(int)),
new DataColumn("name_artis")
{
MaxLength = 255
},
new DataColumn("przv_artis")
{
MaxLength = 150
},
new DataColumn("sp_prd_id")
{
MaxLength = 20
},
new DataColumn("name_post")
{
MaxLength = 255
},
new DataColumn("przv_post")
{
MaxLength = 255
},
new DataColumn("seria")
{
MaxLength = 50
},
new DataColumn("sgodn", typeof(DateTime)),
new DataColumn("sert")
{
MaxLength = 150
},
new DataColumn("sert_date", typeof(DateTime)),
new DataColumn("prcenabnds", typeof(decimal))
{
ExtendedProperties =
{
{ "presision", 12 },
{ "scale", 2 },
}
},
new DataColumn("gr_cena", typeof(decimal))
{
ExtendedProperties =
{
{ "presision", 12 },
{ "scale", 2 },
}
},
new DataColumn("pcena_bnds", typeof(decimal))
{
ExtendedProperties =
{
{ "presision", 12 },
{ "scale", 2 },
}
},
new DataColumn("nds", typeof(int)),
new DataColumn("pcena_nds", typeof(decimal))
{
ExtendedProperties =
{
{ "presision", 12 },
{ "scale", 2 },
}
},
new DataColumn("kol_tov", typeof(decimal))
{
ExtendedProperties =
{
{ "presision", 10 },
{ "scale", 2 },
}
},
//дополнительные поля
new DataColumn("sp_prdr_id", typeof(string))
{
MaxLength = 20
},
new DataColumn("sp_markup", typeof(decimal)),
new DataColumn("p_nds_amnt", typeof(decimal)),
new DataColumn("p_amnt", typeof(decimal)),
new DataColumn("sert_auth")
{
MaxLength = 255
},
new DataColumn("reg_date", typeof(DateTime)),
new DataColumn("vt", typeof(bool)),
new DataColumn("unit", typeof(string))
{
MaxLength = 20
},
new DataColumn("prd_in_mn", typeof(int)),
new DataColumn("excise_tx", typeof(decimal)),
new DataColumn("bll_ntr_id", typeof(string))
{
MaxLength = 30
},
new DataColumn("bar_code")
{
MaxLength = 13
},
new DataColumn("man_date", typeof(DateTime)),
new DataColumn("i_num")
{
MaxLength = 20
},
new DataColumn("i_date", typeof(DateTime)),
new DataColumn("i_sel_name")
{
MaxLength = 255
},
new DataColumn("i_sel_adr")
{
MaxLength = 255
},
new DataColumn("i_sel_inn")
{
MaxLength = 20
},
new DataColumn("i_sel_kpp")
{
MaxLength = 20
},
new DataColumn("i_ship_adr")
{
MaxLength = 255
},
new DataColumn("i_res_name")
{
MaxLength = 255
},
new DataColumn("i_res_id", typeof(int)),
new DataColumn("i_res_adr")
{
MaxLength = 255
},
new DataColumn("i_doc_info")
{
MaxLength = 255
},
new DataColumn("i_bu_id", typeof(int)),
new DataColumn("i_bu_name")
{
MaxLength = 255
},
new DataColumn("i_bu_adr")
{
MaxLength = 255
},
new DataColumn("i_bu_inn")
{
MaxLength = 20
},
new DataColumn("i_bu_kpp")
{
MaxLength = 20
},
new DataColumn("amnt_e_0", typeof(decimal)),
new DataColumn("amnt_w_10", typeof(decimal)),
new DataColumn("amnt_e_10", typeof(decimal)),
new DataColumn("amnt_n_10", typeof(decimal)),
new DataColumn("amnt_w_18", typeof(decimal)),
new DataColumn("amnt_e_18", typeof(decimal)),
new DataColumn("amnt_n_18", typeof(decimal)),
new DataColumn("amnt_n_all", typeof(decimal)),
new DataColumn("amnt_e_all", typeof(decimal)),
new DataColumn("amnt", typeof(decimal)),
new DataColumn("i_del_d", typeof(int)),
new DataColumn("i_del_bd", typeof(int)),
new DataColumn("com_fee_id", typeof(string))
{
MaxLength = 255
},
new DataColumn("com_fee", typeof(decimal)),
new DataColumn("shifr", typeof(string))
{
MaxLength = 255
},
new DataColumn("opt_cena", typeof(decimal)),
new DataColumn("otp_cena", typeof(decimal)),
new DataColumn("rcena", typeof(decimal)),
new DataColumn("storename", typeof(string))
{
MaxLength = 255
},
//поле должно быть в конце из соображений обратной совместимости
new DataColumn("sert_end", typeof(DateTime)),
new DataColumn("country", typeof(string))
{
MaxLength = 60
},
});
var fixColumns = table.Columns.Cast <DataColumn>().Where(c => c.DataType == typeof(decimal) && !c.ExtendedProperties.ContainsKey("presision"));
foreach (var column in fixColumns)
{
column.ExtendedProperties.Add("presision", 12);
column.ExtendedProperties.Add("scale", 2);
}
foreach (var line in document.Lines)
{
var row = table.NewRow();
row.SetField("postid_af", document.FirmCode);
row.SetField("PostNameAF", document.Log.Supplier.FullName);
row.SetField("apt_af", document.Address.Id);
row.SetField("aname_af", document.Address.Name);
row.SetField("ttn", document.ProviderDocumentId);
row.SetField("ttn_date", document.DocumentDate);
if (line.AssortimentPriceInfo != null)
{
row.SetField("id_artis", line.AssortimentPriceInfo.Code);
row.SetField("name_artis", line.AssortimentPriceInfo.Synonym);
row.SetField("przv_artis", line.AssortimentPriceInfo.SynonymFirmCr);
row.SetField("idproducer", line.AssortimentPriceInfo.CodeCr);
}
else
{
if (line.ProductEntity != null)
{
row.SetField("name_artis", line.ProductEntity.CatalogProduct.Name);
}
var producer = SessionHelper.WithSession(s => s.Query <Producer>().FirstOrDefault(p => p.Id == line.ProducerId));
if (producer != null)
{
row.SetField("przv_artis", producer.Name);
}
}
row.SetField("sp_prd_id", line.Code);
row.SetField("name_post", line.Product);
row.SetField("przv_post", line.Producer);
row.SetField("country", line.Country);
row.SetField("seria", line.SerialNumber);
DateTime period;
DateTime?nullPeriod = null;
if (DateTime.TryParse(line.Period, out period))
{
nullPeriod = period;
}
row.SetField("sgodn", nullPeriod);
row.SetField("prd_in_mn", line.ExpireInMonths);
row.SetField("man_date", line.DateOfManufacture);
row.SetField("sert", line.Certificates);
row.SetField("sert_auth", line.CertificateAuthority);
nullPeriod = null;
if (DateTime.TryParse(line.CertificatesDate, out period))
{
nullPeriod = period;
}
row.SetField("sert_date", nullPeriod);
row.SetField("sert_end", line.CertificatesEndDate);
row.SetField("prcenabnds", line.ProducerCostWithoutNDS);
row.SetField("gr_cena", line.RegistryCost);
row.SetField("reg_date", line.RegistryDate);
row.SetField("sp_markup", line.SupplierPriceMarkup);
row.SetField("pcena_bnds", line.SupplierCostWithoutNDS);
row.SetField("pcena_nds", line.SupplierCost);
row.SetField("kol_tov", line.Quantity);
row.SetField("bar_code", line.EAN13);
row.SetField("p_amnt", line.Amount);
row.SetField("nds", line.Nds);
row.SetField("p_nds_amnt", line.NdsAmount);
row.SetField("unit", line.Unit);
row.SetField("vt", line.VitallyImportant);
row.SetField("excise_tx", line.ExciseTax);
row.SetField("bll_ntr_id", line.BillOfEntryNumber);
row.SetField("opt_cena", line.TradeCost);
row.SetField("otp_cena", line.SaleCost);
row.SetField("rcena", line.RetailCost);
row.SetField("shifr", line.Cipher);
row.SetField("sp_prdr_id", line.CodeCr);
var invoice = document.Invoice;
if (invoice != null)
{
row.SetField("i_num", invoice.InvoiceNumber);
row.SetField("i_date", invoice.InvoiceDate);
row.SetField("i_sel_name", invoice.SellerName);
row.SetField("i_sel_adr", invoice.SellerAddress);
row.SetField("i_sel_inn", invoice.SellerINN);
row.SetField("i_sel_kpp", invoice.SellerKPP);
row.SetField("i_ship_adr", invoice.ShipperInfo);
row.SetField("i_res_name", invoice.RecipientName);
row.SetField("i_res_id", invoice.RecipientId);
row.SetField("i_res_adr", invoice.RecipientAddress);
row.SetField("i_doc_info", invoice.PaymentDocumentInfo);
row.SetField("i_bu_id", invoice.BuyerId);
row.SetField("i_bu_name", invoice.BuyerName);
row.SetField("i_bu_adr", invoice.BuyerAddress);
row.SetField("i_bu_inn", invoice.BuyerINN);
row.SetField("i_bu_kpp", invoice.BuyerKPP);
row.SetField("amnt_e_0", invoice.AmountWithoutNDS0);
row.SetField("amnt_e_10", invoice.AmountWithoutNDS0);
row.SetField("amnt_n_10", invoice.NDSAmount10);
row.SetField("amnt_w_10", invoice.Amount10);
row.SetField("amnt_e_18", invoice.AmountWithoutNDS18);
row.SetField("amnt_n_18", invoice.NDSAmount18);
row.SetField("amnt_w_18", invoice.Amount18);
row.SetField("amnt_n_all", invoice.NDSAmount);
row.SetField("amnt_e_all", invoice.AmountWithoutNDS);
row.SetField("amnt", invoice.Amount);
row.SetField("i_del_d", invoice.DelayOfPaymentInDays);
row.SetField("i_del_bd", invoice.DelayOfPaymentInBankDays);
row.SetField("com_fee_id", invoice.CommissionFeeContractId);
row.SetField("com_fee", invoice.CommissionFee);
row.SetField("storename", invoice.StoreName);
}
var columns = table.Columns.Cast <DataColumn>().Where(c => c.MaxLength != -1);
foreach (var column in columns)
{
var value = row[column] as string;
if (value != null &&
value.Length > column.MaxLength)
{
row[column] = value.Slice(column.MaxLength);
}
}
table.Rows.Add(row);
}
using (var writer = new StreamWriter(file, false, Encoding.GetEncoding(866)))
Dbf2.SaveAsMsFoxPro(table, writer);
}
public IEnumerable <IResult> Save()
{
var result = new SaveFileResult(new[] {
Tuple.Create("Отчет (*.dbf)", ".dbf"),
Tuple.Create("Excel (*.xls)", ".xls"),
Tuple.Create("Расширенный Excel (*.xls)", ".xls"),
Tuple.Create("Excel (*.scv)", ".csv"),
Tuple.Create("Здоровые люди (*.scv)", ".csv"),
});
var lines = Lines.Value.Where(x => x.BatchLine != null);
yield return(result);
if (result.Dialog.FilterIndex == 1)
{
using (var writer = result.Writer()) {
var table = new DataTable();
var column = table.Columns.Add("KOD");
column.ExtendedProperties.Add("scale", (byte)9);
column = table.Columns.Add("NAME");
column.ExtendedProperties.Add("scale", (byte)100);
column = table.Columns.Add("KOL", typeof(double));
column.ExtendedProperties.Add("presision", 17);
column.ExtendedProperties.Add("scale", 3);
column = table.Columns.Add("PRICE", typeof(double));
column.ExtendedProperties.Add("presision", 17);
column.ExtendedProperties.Add("scale", 3);
column = table.Columns.Add("NOM_ZAK");
column.ExtendedProperties.Add("scale", (byte)10);
column = table.Columns.Add("NOM_AU");
column.ExtendedProperties.Add("scale", (byte)6);
var goodLines = lines.Where(l => l.OrderLine != null);
foreach (var line in goodLines)
{
var parsedServiceFields = line.BatchLine.ParsedServiceFields.Select(f => f.Value).FirstOrDefault();
table.Rows.Add(
line.OrderLine.Code,
line.OrderLine.ProductSynonym,
line.OrderLine.Count,
line.OrderLine.ResultCost,
line.OrderLine.Id,
parsedServiceFields);
}
Dbf2.Save(table, writer);
}
}
else if (result.Dialog.FilterIndex == 2 || result.Dialog.FilterIndex == 3)
{
var exportServiceFields = result.Dialog.FilterIndex == 3;
using (var writer = result.Writer()) {
ExportExcel(writer.BaseStream, lines, exportServiceFields);
}
}
else if (result.Dialog.FilterIndex == 4)
{
using (var writer = result.Writer()) {
ExportCsv(writer, lines);
}
}
else
{
using (var writer = result.Writer()) {
writer.WriteLine("Номер;Аптека;Дата;Код;Товар;ЗаводШК;Производитель;Количество");
foreach (var line in ReportLines.Value.Where(x => x.BatchLine != null))
{
var reportLine = line.BatchLine.ParsedServiceFields.Where(f => f.Key == "ReportData")
.Select(f => f.Value)
.FirstOrDefault();
writer.WriteLine(reportLine);
}
}
}
}
private void ProcessUser(ISession session, uint userId)
{
log.Debug($"Обработка пользователя {userId}");
IList <NamedOffer> offers;
using (var trx = session.BeginTransaction()) {
offers = PriceHelper.QueryOffers(session, userId);
trx.Commit();
}
var config = session.Query <FtpConfig>().Where(x => x.User.Id == userId).ToList();
foreach (var supplierConfig in config)
{
using (var cleander = new FileCleaner())
using (var client = new FtpClient()) {
var url = supplierConfig.PriceUrl;
try {
if (!String.IsNullOrEmpty(url))
{
OpenFtp(new Uri(url), client);
var priceOffers = offers.Where(x => x.PriceList.Id.Price.Supplier.Id == supplierConfig.Supplier.Id)
.ToArray();
var tmp = cleander.TmpFile();
Dbf2.SaveAsDbf4(DbfAsna.Price(priceOffers), tmp);
UploadFile(client, tmp, new Uri(url));
}
} catch (Exception e) {
log.Error($"Не удалось выгрузить прайс-лист поставщика {supplierConfig.Supplier.Name} ({supplierConfig.Supplier.Id}) в {url}", e);
#if DEBUG
throw;
#endif
}
url = supplierConfig.WaybillUrl;
if (!String.IsNullOrEmpty(url))
{
try {
var waybillIds = session.CreateSQLQuery(@"
select dh.Id, d.RowId, ds.Id as SendLogId
from Logs.DocumentSendLogs ds
join Logs.Document_logs d on d.RowId = ds.DocumentId
join Documents.DocumentHeaders dh on dh.DownloadId = d.RowId
where ds.UserId = :userId
and ds.Committed in (0, 2)
and d.FirmCode = :supplierId
order by d.LogTime desc
limit 400;")
.SetParameter("userId", userId)
.SetParameter("supplierId", supplierConfig.Supplier.Id)
.List <object[]>();
foreach (var pair in waybillIds)
{
var doc = session.Load <Document>(Convert.ToUInt32(pair[0]));
var tmp = cleander.TmpFile();
Dbf2.SaveAsDbf4(DbfAsna.Waybill(session, doc), tmp);
var part = url;
if (!part.EndsWith("/"))
{
part += "/";
}
UploadFile(client, tmp, new Uri(part + pair[1] + ".dbf"));
using (var trx = session.BeginTransaction()) {
var sendLog = session.Load <DocumentSendLog>(Convert.ToUInt32(pair[2]));
sendLog.Commit();
session.Flush();
trx.Commit();
}
}
} catch (Exception e) {
log.Error($"Не удалось выгрузить накладную поставщика {supplierConfig.Supplier.Name} ({supplierConfig.Supplier.Id}) в {url}", e);
#if DEBUG
throw;
#endif
}
}
}
}
}
