public void ApplyPendingScripts(SqlConnection connection, IMigratorConfiguration configuration, IEnumerable <PendingItem> scripts)
{
foreach (var updateScript in scripts)
{
ClasspathScripts.Add(updateScript);
AppliedItem appliedScript = new AppliedItem
{
Type = updateScript.Type,
Executed = DateTime.Now,
Name = updateScript.Name,
Version = updateScript.Version,
Checksum = updateScript.Checksum
};
if (updateScript.Type == ItemType.Repeatable)
{
AppliedItem oldItem = DbItems.FirstOrDefault(s => Equals(updateScript.Version, s.Version));
if (oldItem != null)
{
DbItems.Remove(oldItem);
}
}
DbItems.Add(appliedScript);
AppliedItems.Add(appliedScript);
Log.InfoFormat("Running script {0} with version {1}", updateScript.Name, updateScript.Version);
}
}
public static bool DeleteItem(Int32 itemId)
{
var dbItems = new DbItems();
var result = dbItems.Delete(itemId);
return(result);
}
public static string LoadProposalItems(Int32 id)
{
var items = DbItems.LoadAll(id);
var json = new JavaScriptSerializer();
return(json.Serialize(items));
}
public static string DeleteProposal(Int32 id)
{
var result = "";
var dbProposal = new DbProposals();
var db = DbaseTools.CreateDbase();
try
{
db.Open();
db.BeginTransaction();
var delItems = true;
var items = DbItems.LoadAll(id, db);
if (items.Count > 0)
{
var dbItems = new DbItems();
foreach (var i in items)
{
delItems = dbItems.Delete(i.ID, db);
if (!delItems)
{
db.Rollback();
result = "Error on deleting proposal item " + i.ID + "!";
break;
}
}
}
if (delItems)
{
var delProposal = dbProposal.Delete(id, db);
if (!delProposal)
{
db.Rollback();
result = "Error on deleting proposal!";
}
db.Commit();
result = "OK";
}
}
catch
{
result = "Error on database!";
}
finally
{
db.Close();
db.Dispose();
db = null;
}
return(result);
}
/// <summary>
/// Parses informations from <see cref="HtmlNode"/> array and adds information to <see cref="AllegroAppContext"/> database.
/// </summary>
/// <param name="nodes">Array of <article> nodes.</param>
/// <param name="paths"><see cref="List{T}">List</see> of paths to photos.</param>
/// <param name="page">Number of page.</param>
/// <param name="querry">Query from TextBox</param>
public void Parse(HtmlNode[] nodes, List <string> paths, int page, string querry)
{
//klasy znaczników
#region tag classes
string regularAuctionName = ".//a[@class='_w7z6o _uj8z7 meqh_en mpof_z0 mqu1_16 _9c44d_2vTdY ']"; //normalna aukcja
string clothingName = ".//a[@class='_w7z6o _uj8z7 meqh_en mpof_z0 mqu1_16 _9c44d_2vTdY m7er_k4 msa3_z4'";
string otherClothingName = ".//a[@class='_w7z6o _uj8z7 meqh_en mpof_z0 mqu1_16 _9c44d_2vTdY m9qz_yq m7er_k4 msa3_z4'";
string clothingName3 = ".//a[@class='_1h7wt _15mod mj9z_5r _07bcb_2W89U'";
string freeShippingAuctionName = ".//a[@class='_w7z6o _uj8z7 meqh_en mpof_z0 mqu1_16 _9c44d_2vTdY m9qz_yq ']"; //aukcja z darmową dostawą
string priceInfo = ".//span[@class='_1svub _lf05o']"; //informacja o cenie
string paramList = ".//dl[@class='mp4t_0 m3h2_0 mryx_0 munh_0 mg9e_0 mvrt_0 mj7a_0 mh36_0 meqh_en msa3_z4 _1vx3o']"; //lista parametrów danego produktu - zawsze inna
string hyperlink = ".//a[@class='msts_9u mg9e_0 mvrt_0 mj7a_0 mh36_0 mpof_ki m389_6m mx4z_6m m7f5_6m mse2_k4 m7er_k4 _9c44d_1ILhl ']"; //hiperłącze
string allegroSmart = ".//i[@class='_9c44d_2UYuR _9c44d_1DKTg']"; //allegro smart
#endregion
DbQueryInfo queryLoad = new DbQueryInfo();
queryLoad.Add(querry, DateTime.Now);
//wyciągnięcie informacji z tych nodów
int x = 0;
foreach (HtmlNode item in nodes)
{
var index = Array.IndexOf(nodes, item);
if (item.SelectSingleNode(regularAuctionName) != null)
{
var ItemName = item.SelectSingleNode(regularAuctionName).InnerText;
var ItemPrice = item.SelectSingleNode(priceInfo).InnerText;
ItemPrice = ItemPrice.Replace(",", ".");
ItemPrice = ItemPrice.Replace(" ", "");
ItemPrice = ItemPrice.Replace("zł", "");
var decimalPrice = Decimal.Parse(ItemPrice, CultureInfo.InvariantCulture);
var link = item.SelectSingleNode(hyperlink).GetAttributeValue("href", "http://allegro.pl");
bool isAllegroSmart = (item.SelectSingleNode(allegroSmart) != null);
DbItems result = new DbItems();
result.Add(queryLoad.queryInfo.Id, ItemName, decimalPrice, isAllegroSmart, page, paths[x], link);
var ParametersNode = item.SelectSingleNode(paramList);
string tmp = "";
string tmp2 = "";
if (ParametersNode != null)
{
var ParametersList = ParametersNode.ChildNodes;
foreach (var item2 in ParametersList)
{
if (item2.Name == "dt")
{
tmp = item2.InnerText;
}
else
{
tmp2 = item2.InnerText;
DbItemParams param = new DbItemParams();
param.Add(result.item.Id, queryLoad.queryInfo.Id, tmp, tmp2);
}
}
}
x++;
}
else if (item.SelectSingleNode(freeShippingAuctionName) != null)
{
var ItemName = item.SelectSingleNode(freeShippingAuctionName).InnerText;
var ItemPrice = item.SelectSingleNode(priceInfo).InnerText;
ItemPrice = ItemPrice.Replace(",", ".");
ItemPrice = ItemPrice.Replace(" ", "");
ItemPrice = ItemPrice.Replace("zł", "");
var decimalPrice = Decimal.Parse(ItemPrice, CultureInfo.InvariantCulture);
var ParametersNode = item.SelectSingleNode(paramList);
var link = item.SelectSingleNode(hyperlink).GetAttributeValue("href", "http://allegro.pl");
bool isAllegroSmart = (item.SelectSingleNode(allegroSmart) != null);
string tmp = "";
string tmp2 = "";
DbItems result = new DbItems();
result.Add(queryLoad.queryInfo.Id, ItemName, decimalPrice, isAllegroSmart, page, paths[x], link);
if (ParametersNode != null)
{
var ParametersList = ParametersNode.ChildNodes;
foreach (var item2 in ParametersList)
{
if (item2.Name == "dt")
{
tmp = item2.InnerText;
}
else
{
tmp2 = item2.InnerText;
DbItemParams param = new DbItemParams();
param.Add(result.item.Id, queryLoad.queryInfo.Id, tmp, tmp2);
}
}
}
x++;
}
if (x == 60)
{
break;
}
}
}
private void CreateExcel(DbProposals proposal)
{
var fileName = proposal.ProposalName;
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(fileName);
//Company
var aRow = 2;
ws.Cells["A1"].Value = proposal.CompanyName;
if (proposal.CompanyAddress != "" && proposal.CompanyAddress != null)
{
ws.Cells["A" + aRow].Value = proposal.CompanyAddress;
aRow++;
}
if (proposal.CompanyCity != "" && proposal.CompanyCity != null)
{
ws.Cells["A" + aRow].Value = proposal.CompanyCity;
aRow++;
}
if (proposal.CompanyPIN.ToString() != "" && proposal.CompanyPIN != null)
{
ws.Cells["A" + aRow].Value = "OIB: " + proposal.CompanyPIN.ToString();
}
var dRow = 1;
if (proposal.CompanyPhone != "" && proposal.CompanyPhone != null)
{
ws.Cells["D" + dRow].Value = "TEL.: " + proposal.CompanyPhone;
dRow++;
}
if (proposal.CompanyFax != "" && proposal.CompanyFax != null)
{
ws.Cells["D" + dRow].Value = "FAX: " + proposal.CompanyFax;
dRow++;
}
if (proposal.CompanyEmail != "" && proposal.CompanyEmail != null)
{
ws.Cells["D" + dRow].Value = "E-MAIL: " + proposal.CompanyEmail;
dRow++;
}
if (proposal.CompanyIBAN != "" && proposal.CompanyIBAN != null)
{
ws.Cells["D" + dRow].Value = "IBAN: " + proposal.CompanyIBAN;
}
for (int i = 1; i <= aRow; i++)
{
ws.Cells["A" + i + ":B" + i].Merge = true;
}
for (int i = 1; i <= dRow; i++)
{
ws.Cells["D" + i + ":F" + i].Merge = true;
}
ws.Cells["A" + ((aRow >= dRow) ? aRow : dRow) + ":F" + ((aRow >= dRow) ? aRow : dRow)].Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
ws.Cells["D1:F" + dRow].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells["A1:D" + aRow].Style.Font.Bold = true;
//Client
var clBeginRow = (aRow >= dRow) ? aRow + 5 : dRow + 5;
var clRow = clBeginRow + 1;
ws.Cells["A" + (clBeginRow)].Value = proposal.ClientName;
if (proposal.ClientAddress != "" && proposal.ClientAddress != null)
{
ws.Cells["A" + clRow].Value = proposal.ClientAddress;
clRow++;
}
if (proposal.ClientCity != "" && proposal.ClientCity != null)
{
ws.Cells["A" + clRow].Value = proposal.ClientCity;
clRow++;
}
if (proposal.ClientPhone != "" && proposal.ClientPhone != null)
{
ws.Cells["A" + clRow].Value = "TEL.: " + proposal.ClientPhone;
clRow++;
}
if (proposal.ClientEmail != "" && proposal.ClientEmail != null)
{
ws.Cells["A" + clRow].Value = "E-MAIL: " + proposal.ClientEmail;
clRow++;
}
if (proposal.ClientPIN.ToString() != "" && proposal.ClientPIN != null)
{
ws.Cells["A" + clRow].Value = "OIB:" + proposal.ClientPIN.ToString();
}
for (int i = clBeginRow; i <= clRow; i++)
{
ws.Cells["A" + i + ":F" + i].Merge = true;
}
ws.Cells["A" + clBeginRow + ":A" + clRow].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
//ws.Cells["E9:F9"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
//ws.Cells["F9:F14"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
//ws.Cells["E14:F14"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//ws.Cells["E9:F14"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
//Items title
var itRow = clRow + 4;
ws.Cells["A" + itRow + ":F" + itRow].Merge = true;
ws.Cells["A" + itRow].Value = proposal.ItemsTitle;
ws.Cells["A" + itRow].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A" + itRow].Style.Font.UnderLine = true;
ws.Cells["A" + (itRow + 3)].Value = "BR."; //todo: jezik proslijediti iz postavki...
ws.Cells["B" + (itRow + 3)].Value = "NAZIV ARTIKLA/USLUGE";
ws.Cells["C" + (itRow + 3)].Value = "J. MJ.";
ws.Cells["D" + (itRow + 3)].Value = "KOL.";
ws.Cells["E" + (itRow + 3)].Value = "CIJENA";
ws.Cells["F" + (itRow + 3)].Value = "VRIJEDNOST";
ws.Cells["A" + (itRow + 3) + ":F" + (itRow + 3)].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//Items
var items = DbItems.LoadAll(proposalId);
var row = itRow + 4;
if (items != null && items.Count > 0)
{
for (int i = 0; i < items.Count; i++)
{
ws.Cells["A" + row].Value = items[i].ItemNumber;
ws.Cells["B" + row].Value = items[i].ItemText;
ws.Cells["C" + row].Value = items[i].Unit;
ws.Cells["D" + row].Value = items[i].Quantity;
ws.Cells["E" + row].Value = items[i].UnitPrice;
ws.Cells["F" + row].Value = items[i].TotalPrice;
ws.Row(row).Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Row(row).Style.Fill.BackgroundColor.SetColor((row % 2 == 0) ? Color.Transparent : Color.FromArgb(240, 240, 240));
if (i < (items.Count - 1))
{
row++;
}
}
ws.Cells["D" + (itRow + 4) + ":F" + row].Style.Numberformat.Format = "0.00";
ws.Cells["A" + row + ":F" + row].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//Total
ws.Cells["E" + (row + 2)].Value = "IZNOS:";
ws.Cells["F" + (row + 2)].Value = proposal.Amount + " kn"; //todo: valutu proslijediti iz postavki...
ws.Cells["F" + (row + 2)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells["F" + (row + 2)].Style.Numberformat.Format = "0.00";
if (proposal.Tax > 0)
{
ws.Cells["E" + (row + 3)].Value = "PDV:";
ws.Cells["F" + (row + 3)].Value = proposal.Tax + " kn";
ws.Cells["F" + (row + 3)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells["F" + (row + 3)].Style.Numberformat.Format = "0.00";
}
else
{
ws.Cells["E" + (row + 3) + ":F" + (row + 3)].Merge = true;
ws.Cells["E" + (row + 3)].Value = "PDV nije obračunat";
}
ws.Cells["E" + (row + 4)].Value = "UKUPNO:";
ws.Cells["F" + (row + 4)].Value = proposal.Total + " kn";
ws.Cells["F" + (row + 4)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells["F" + (row + 4)].Style.Numberformat.Format = "0.00";
ws.Cells["E" + (row + 2) + ":F" + (row + 4)].Style.Font.Bold = true;
ws.Cells["E" + (row + 4) + ":F" + (row + 4)].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
//Note & Signature
ws.Cells["B" + (row + 7)].Value = proposal.Note;
ws.Cells["E" + (row + 9) + ":F" + (row + 9)].Merge = true;
ws.Cells["E" + (row + 9)].Value = proposal.Signature;
if (proposal.Signature.Length > 22)
{
ws.Row(row + 9).Height = 32;
}
if (proposal.Signature.Length > 44)
{
ws.Row(row + 9).Height = 64;
}
//Horizontal alignment for all cells
ws.Cells["A1:F" + (row + 9)].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
setColumnWidth(ws, 1, 6);
setColumnWidth(ws, 2, 40);
setColumnWidth(ws, 3, 7);
setColumnWidth(ws, 4, 9);
setColumnWidth(ws, 5, 11);
setColumnWidth(ws, 6, 14);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + fileName + ".xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
}
public static string SaveProposal(DbProposals proposal, DbItems[] items, int[] deletedItems)
{
var result = "";
var dbProposals = new DbProposals(proposal.ID, proposal.ProposalName, DateTime.Now, Home.AppUser.ID,
proposal.CompanyName, proposal.CompanyAddress, proposal.CompanyCity, proposal.CompanyPIN, proposal.CompanyPhone, proposal.CompanyFax,
proposal.CompanyEmail, proposal.CompanyIBAN, proposal.ClientName, proposal.ClientAddress, proposal.ClientCity, proposal.ClientPhone,
proposal.ClientEmail, proposal.ClientPIN, proposal.ItemsTitle, proposal.Amount, proposal.Tax, proposal.Total, proposal.Note, proposal.Signature);
var db = DbaseTools.CreateDbase();
try
{
db.Open();
db.BeginTransaction();
var deleteResult = true;
foreach (var id in deletedItems)
{
deleteResult = DeleteItem(Convert.ToInt32(id));
if (!deleteResult)
{
db.Rollback();
result = "Error on deleting proposal item with ID = " + id + "!";
break;
}
}
var proposalId = dbProposals.Save(db);
if (proposalId != -1)
{
//prošao insert/update...
//update - postavi porposalId na id koji se prenosi iz proposal
if (proposalId == 0)
{
proposalId = proposal.ID;
}
for (int i = 0; i < items.Length; i++)
{
var dbItems = new DbItems(items[i].ID, proposalId, items[i].ItemNumber, items[i].ItemText, items[i].Unit,
items[i].Quantity, items[i].UnitPrice, items[i].TotalPrice);
var res = dbItems.Save(db);
if (!res)
{
db.Rollback();
result = "Error on saving proposal items!";
break;
}
}
db.Commit();
result = "OK_" + proposalId.ToString();
}
else
{
db.Rollback();
result = "Error on saving proposal!";
}
}
catch
{
result = "Error on database!";
}
finally
{
db.Close();
db.Dispose();
db = null;
}
return(result /*? "OK" : "Error saving proposal!"*/);
}
public Cart(DbItems items, int quantity, double itemTotalPrice)
{
Items = items;
Quantity = quantity;
ItemTotalPrice = itemTotalPrice;
}
