private void InsertIntoBaseB2B()
{
foreach (schemas.Notification NC in NClist)
{
commStr = @"INSERT IGNORE tenderbase.notification (notificationType,href,orderName,notificationNumber,publishDate,orgName,orgINN,region,have_eng,type)
VALUES (@notificationType,@href,@orderName,@notificationNumber,@publishDate,@orgName,@orgINN,@region,@have_eng,'B2B')";
comm = new MySqlCommand(commStr, conn);
comm.Parameters.AddWithValue("@notificationType", NC.notice.notificationType);
comm.Parameters.AddWithValue("@href", NC.notice.href);
comm.Parameters.AddWithValue("@orderName", NC.notice.orderName);
comm.Parameters.AddWithValue("@notificationNumber", NC.notice.notificationNumber);
comm.Parameters.AddWithValue("@publishDate", NC.notice.publishDate);
comm.Parameters.AddWithValue("@orgName", NC.notice.orgName);
comm.Parameters.AddWithValue("@orgINN", NC.notice.orgINN);
comm.Parameters.AddWithValue("@region", RegionParse(NC.notice.region));
comm.Parameters.AddWithValue("@have_eng", (NParse.NavalnyParse(NC.notice.orderName) ? "1" : "0"));
if (ExecuteNonQuery(comm) == 0) // если команда выдает 0 - значит запись уже есть, обновляем publishDate, иначе, вставляем как новую
{
commStr = @"UPDATE tenderbase.notification SET publishDate=@publishDate WHERE notificationNumber=@notificationNumber";
comm = new MySqlCommand(commStr, conn);
comm.Parameters.AddWithValue("@publishDate", NC.notice.publishDate);
comm.Parameters.AddWithValue("@notificationNumber", NC.notice.notificationNumber);
ExecuteNonQuery(comm);
}
else
{
string nID = GetLastIDfromNotice();
// вставляем фейковый лот
commStr = @"INSERT INTO tenderbase.lot (notificationID,currencyName,maxPrice,type) VALUES (@notificationID,@currencyName,@maxPrice,'B2B')";
comm = new MySqlCommand(commStr, conn);
comm.Parameters.AddWithValue("@notificationID", nID);
comm.Parameters.AddWithValue("@currencyName", NC.oneLot.currencyName);
comm.Parameters.AddWithValue("@maxPrice", NC.oneLot.maxPrice);
ExecuteNonQuery(comm);
foreach (string oneOKDP in NC.notice.okdpPath.Split(','))
{
comm = new MySqlCommand("INSERT INTO tenderbase.products (notificationID,code) VALUES (@notificationID,@code)", conn);
comm.Parameters.AddWithValue("@notificationID", nID);
comm.Parameters.AddWithValue("@code", oneOKDP);
ExecuteNonQuery(comm);
}
}
}
}
public void InsertIntoBase()
{
// по каждой разобранной записи
// int count =0;
// int count1 =0;
foreach (schemas.Notification NC in Nlist)
{
string notifType = NC.notice.placingWay_code;
// вставляем основу notificationEF и получаем последний ID
string commStr = @"INSERT IGNORE tenderbase.notification (dateinsert,notificationNumber,notificationType,orderName,publishDate,href,placingWay_code,orgName,orgRegNum,placementFeature_code,placementFeature_name,ep_name,ep_url,region,have_eng,submissionCloseDateTime,type,orgINN)
VALUES (now(),@notificationNumber,@notificationType,@orderName,@publishDate,@href,@placingWay_code,@orgName,@orgRegNum,@placementFeature_code,@placementFeature_name,@ep_name,@ep_url,@region,@have_eng,@submissionCloseDateTime,'44',@orgINN)";
comm = new MySqlCommand(commStr, conn);
comm.CommandTimeout = 0;
comm.Parameters.AddWithValue("@notificationNumber", NC.notice.notificationNumber);
comm.Parameters.AddWithValue("@notificationType", notifType);
comm.Parameters.AddWithValue("@orderName", NC.notice.orderName);
comm.Parameters.AddWithValue("@publishDate", NC.notice.publishDate);
comm.Parameters.AddWithValue("@href", NC.notice.href);
comm.Parameters.AddWithValue("@placingWay_code", NC.notice.placingWay_code);
comm.Parameters.AddWithValue("@orgName", NC.notice.orgName);
comm.Parameters.AddWithValue("@orgRegNum", NC.notice.orgRegNum);
comm.Parameters.AddWithValue("@placementFeature_code", NC.notice.placementFeature_code);
comm.Parameters.AddWithValue("@placementFeature_name", NC.notice.placementFeature_name);
comm.Parameters.AddWithValue("@ep_name", NC.notice.ep_name);
comm.Parameters.AddWithValue("@ep_url", NC.notice.ep_url);
comm.Parameters.AddWithValue("@region", region);
comm.Parameters.AddWithValue("@have_eng", (NParse.NavalnyParse(NC.notice.orderName) ? "1" : "0"));
comm.Parameters.AddWithValue("@submissionCloseDateTime", NC.notice.submissionCloseDateTime);
comm.Parameters.AddWithValue("@orgINN", NC.notice.orgINN);
if (ExecuteNonQuery(comm) == 0) // если команда выдает 0 - значит запись уже есть, обновляем publishDate, иначе, вставляем как новую
{
commStr = @"UPDATE tenderbase.notification SET publishDate=@publishDate,submissionCloseDateTime=@submissionCloseDateTime,type='44' WHERE notificationNumber=@notificationNumber";
comm = new MySqlCommand(commStr, conn);
comm.CommandTimeout = 0;
comm.Parameters.AddWithValue("@publishDate", NC.notice.publishDate);
comm.Parameters.AddWithValue("@submissionCloseDateTime", NC.notice.submissionCloseDateTime);
comm.Parameters.AddWithValue("@notificationNumber", NC.notice.notificationNumber);
ExecuteNonQuery(comm);
// Console.WriteLine("Dup {0}",count++);
}
else
{
string ncID = GetLastIDfromNotification(ETables.notification);
// вставляем DocumentMetas
StringBuilder documentMetasStr = new StringBuilder();
for (int i = 0; i < NC.documents.Count; i++)
{
documentMetasStr.Append("(" + ncID + "," + NotNull(NC.documents[i].fileName) + "," + NotNull(NC.documents[i].docDescription) + "," + NotNull(NC.documents[i].url) + "),");
}
if (!string.IsNullOrEmpty(documentMetasStr.ToString()))
{
documentMetasStr = documentMetasStr.Remove(documentMetasStr.Length - 1, 1);
commStr = "INSERT INTO tenderbase.documents (notificationID,fileName,docDescription,url) VALUES " + documentMetasStr;
comm = new MySqlCommand(commStr, conn);
comm.CommandTimeout = 0;
ExecuteNonQuery(comm);
}
// вставляем lots
#region Lots
for (int i = 0; i < NC.lots.Count; i++)
{
// вставляем основу и узнаем последний id лота
commStr = @"INSERT INTO tenderbase.lot (notificationID,subject,currencyCode,currencyName,maxPrice,type)
VALUES (@notificationID,@subject,@currencyCode,@currencyName,@maxPrice,'44')";
comm = new MySqlCommand(commStr, conn);
comm.CommandTimeout = 0;
comm.Parameters.AddWithValue("@notificationID", ncID);
comm.Parameters.AddWithValue("@subject", NC.lots[i].subject);
comm.Parameters.AddWithValue("@currencyCode", NC.lots[i].currencyCode);
comm.Parameters.AddWithValue("@currencyName", NC.lots[i].currencyName);
comm.Parameters.AddWithValue("@maxPrice", NC.lots[i].maxPrice);
ExecuteNonQuery(comm);
string lotID = GetLastIDfromNotification(ETables.lot);
// разбираем products
StringBuilder productStr = new StringBuilder();
for (int iprod = 0; iprod < NC.products.Count; iprod++)
{
productStr.Append("(" + ncID + "," + lotID + "," + NotNull(NC.products[iprod].code) + "," + NotNull(NC.products[iprod].name) + "," + NotNull(NC.products[iprod].price) + "," + NotNull(NC.products[iprod].fullname) + "),");
}
if (!string.IsNullOrEmpty(productStr.ToString()))
{
productStr = productStr.Remove(productStr.Length - 1, 1);
commStr = "INSERT INTO tenderbase.products (notificationID,lotID,code,name,price,fullname) VALUES " + productStr;
comm = new MySqlCommand(commStr, conn);
comm.CommandTimeout = 0;
ExecuteNonQuery(comm);
}
}
// Console.WriteLine("Norm {0}", count1++);
}
#endregion
}
}
public void InsertIntoBase()
{
if (Nlist.Count > 0)
{
Console.WriteLine("{0}. Notice {1}: {2}", region, Nlist[0].notice.notificationType, Nlist.Count.ToString());
}
// по каждой записи
foreach (schemas.Notification NC in Nlist)
{
string nType = NC.notice.notificationType;
// вставляем основу, получаем последний ID
commStr = @"INSERT IGNORE tenderbase.notification (dateinsert,notificationNumber,notificationType,orderName,purchaseMethodCode,purchaseMethodName,submissionCloseDateTime,publishDate,status,modificationDescription,modificationDate,ep_name,ep_url,orgName,orgINN,orgOGRN,region,have_eng,type)
VALUES (now(),@notificationNumber,@notificationType,@orderName,@purchaseMethodCode,@purchaseMethodName,@submissionCloseDateTime,@publishDate,@status,@modificationDescription,@modificationDate,@ep_name,@ep_url,@orgName,@orgINN,@orgOGRN,@region,@have_eng,'223')";
comm = new MySqlCommand(commStr, conn);
comm.Parameters.AddWithValue("@notificationNumber", NC.notice.notificationNumber);
comm.Parameters.AddWithValue("@notificationType", nType);
comm.Parameters.AddWithValue("@orderName", NC.notice.orderName);
comm.Parameters.AddWithValue("@purchaseMethodCode", NC.notice.purchaseMethodCode);
comm.Parameters.AddWithValue("@purchaseMethodName", NC.notice.purchaseMethodName);
comm.Parameters.AddWithValue("@submissionCloseDateTime", Common.IsDate(NC.notice.submissionCloseDateTime));
comm.Parameters.AddWithValue("@publishDate", Common.IsDate(NC.notice.publishDate));
comm.Parameters.AddWithValue("@status", NC.notice.status);
comm.Parameters.AddWithValue("@modificationDescription", NC.notice.modificationDescription);
comm.Parameters.AddWithValue("@modificationDate", Common.IsDate(NC.notice.modificationDate));
comm.Parameters.AddWithValue("@ep_name", NC.notice.ep_name);
comm.Parameters.AddWithValue("@ep_url", NC.notice.ep_url);
comm.Parameters.AddWithValue("@orgName", NC.notice.orgName);
comm.Parameters.AddWithValue("@orgINN", NC.notice.orgINN);
comm.Parameters.AddWithValue("@orgOGRN", NC.notice.orgOGRN);
comm.Parameters.AddWithValue("@region", region);
comm.Parameters.AddWithValue("@have_eng", (NParse.NavalnyParse(NC.notice.orderName) ? "1" : "0"));
if (ExecuteNonQuery(comm) == 0) // если команда выдает 0 - значит запись уже есть, обновляем publishDate, иначе, вставляем как новую
{
commStr = @"UPDATE tenderbase.notification SET publishDate=@publishDate,submissionCloseDateTime=@submissionCloseDateTime WHERE notificationNumber=@notificationNumber";
comm = new MySqlCommand(commStr, conn);
comm.Parameters.AddWithValue("@publishDate", NC.notice.publishDate);
comm.Parameters.AddWithValue("@submissionCloseDateTime", NC.notice.submissionCloseDateTime);
comm.Parameters.AddWithValue("@notificationNumber", NC.notice.notificationNumber);
ExecuteNonQuery(comm);
}
else
{
string Nid = GetLastIDfromNotice(ETables.notice);
// вставляем документы
StringBuilder attachementsStr = new StringBuilder();
for (int i = 0; i < NC.documents.Count; i++)
{
attachementsStr.Append("(" + Nid + "," + NotNull(NC.documents[i].fileName) + "," + NotNull(NC.documents[i].docDescription) + "," + NotNull(NC.documents[i].url) + "),");
}
if (!string.IsNullOrEmpty(attachementsStr.ToString()))
{
attachementsStr = attachementsStr.Remove(attachementsStr.Length - 1, 1);
commStr = "INSERT INTO tenderbase.documents (notificationID,fileName,docDescription,url) VALUES " + attachementsStr;
comm = new MySqlCommand(commStr, conn);
ExecuteNonQuery(comm);
}
// вставляем lots
for (int i = 0; i < NC.lots.Count; i++)
{
commStr = @"INSERT INTO tenderbase.lot (notificationID,subject,currencyCode,currencyName,maxPrice,type)
VALUES (@notificationID,@subject,@currencyCode,@currencyName,@maxPrice,'223')";
comm = new MySqlCommand(commStr, conn);
comm.Parameters.AddWithValue("@notificationID", Nid);
comm.Parameters.AddWithValue("@subject", NC.lots[i].subject);
comm.Parameters.AddWithValue("@maxPrice", Common.IsNumeric(NC.lots[i].maxPrice));
comm.Parameters.AddWithValue("@currencyCode", NC.lots[i].currencyCode);
comm.Parameters.AddWithValue("@currencyName", NC.lots[i].currencyName);
ExecuteNonQuery(comm);
string lotID = GetLastIDfromNotice(ETables.lot);
// разбираем LotItems - products
StringBuilder lotItemStr = new StringBuilder();
for (int li = 0; li < NC.products.Count; li++)
{
if (NC.products[li].lotID == i)
{
lotItemStr.Append("(" + lotID + "," + Nid + "," + NotNull(NC.products[li].code) + "," + NotNull(NC.products[li].name) + "),");
}
}
if (!string.IsNullOrEmpty(lotItemStr.ToString()))
{
lotItemStr = lotItemStr.Remove(lotItemStr.Length - 1, 1);
commStr = "INSERT INTO tenderbase.products (lotID,notificationID,code,name) VALUES " + lotItemStr;
comm = new MySqlCommand(commStr, conn);
ExecuteNonQuery(comm);
}
}
}
}
}