private List <EquipmentCost> search()
{
List <EquipmentCost> list = new List <EquipmentCost>();
connection = new MySqlConnection(connString);
try
{
connection.Open();
command = new MySqlCommand(sql, connection);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
EquipmentCost eqpct = new EquipmentCost();
eqpct.EquipmentId = dataReader.GetInt32(0);
eqpct.Cost = dataReader.GetInt32(1);
list.Add(eqpct);
}
dataReader.Close();
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
return(null);
}
return(list);
}
// POST api/equipmentcost
public EquipmentCost Post(EquipmentCost eqpCost)
{
if (eqpCostService.Insert(eqpCost))
{
return(eqpCost);
}
return(null);
}
// PUT api/equipmentcost/5
public EquipmentCost Put(int id, EquipmentCost newEquipmentCost)
{
if (eqpCostService.SearchId(id).Count > 0)
{
if (eqpCostService.Edit(newEquipmentCost))
{
return(newEquipmentCost);
}
}
return(null);
}
public string SetFilterCosts(string from, string to, string filter, int companyId)
{
var res = string.Format(
CultureInfo.InvariantCulture,
@"{0}|{1}{2}",
from,
to,
filter);
Session["EquipmentFilterCosts"] = res.ToUpperInvariant();
return(EquipmentCost.JsonList(EquipmentCost.Filter(from, to, filter, companyId)));
}
// DELETE api/equipmentcost/5
public EquipmentCost Delete(int id)
{
EquipmentCost oldEquipmentCost = new EquipmentCost();
var data = eqpCostService.SearchId(id);
if (data.Count > 0)
{
oldEquipmentCost.SetEquipmentCost(data.ElementAt(0));
if (eqpCostService.DeleteId(id))
{
return(oldEquipmentCost);
}
}
return(null);
}
/// <summary>
/// Saves the equipment.
/// </summary>
/// <returns>Equipment Object</returns>
public Equipment SaveEquipment()
{
SqlDataReader result;
DatabaseConnection dbconn = new DatabaseConnection();
SqlCommand command = new SqlCommand();
SqlConnection connection = new SqlConnection(dbconn.SQLSEVERConnString);
try
{
connection.Open();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "InsertUpdate_Equipment";
command.Parameters.Add(dbconn.GenerateParameterObj("@EquipmentID", SqlDbType.Int, EquipmentID.ToString(), 0));
command.Parameters.Add(dbconn.GenerateParameterObj("@EquipmentName", SqlDbType.VarChar, EquipmentName.ToString(), 50));
command.Parameters.Add(dbconn.GenerateParameterObj("@EquipmentDescription", SqlDbType.VarChar, EquipmentDescription.ToString(), 400));
command.Parameters.Add(dbconn.GenerateParameterObj("@EquipmentTypeID", SqlDbType.Int, EquipmentTypeID.ToString(), 0));
command.Parameters.Add(dbconn.GenerateParameterObj("@EquipmentWeight", SqlDbType.Decimal, EquipmentWeight.ToString(), 0));
command.Parameters.Add(dbconn.GenerateParameterObj("@EquipmentCost", SqlDbType.Int, EquipmentCost.ToString(), 0));
command.Parameters.Add(dbconn.GenerateParameterObj("@BookID", SqlDbType.Int, BookID.ToString(), 0));
command.Parameters.Add(dbconn.GenerateParameterObj("@Upgradable", SqlDbType.Bit, Upgradable.ToString(), 0));
result = command.ExecuteReader();
result.Read();
SetReaderToObject(ref result);
}
catch
{
Exception e = new Exception();
this._insertUpdateOK = false;
this._insertUpdateMessage.Append(e.Message + " Inner Exception= " + e.InnerException);
throw e;
}
finally
{
command.Dispose();
connection.Close();
}
return(this);
}
public bool Edit(EquipmentCost eqpct)
{
sql = "UPDATE equipment_cost SET Equipment_equipmentID='" + eqpct.EquipmentId + "', equipCost='" + eqpct.Cost + "' WHERE Equipment_equipmentID='" + eqpct.EquipmentId + "'";
return(EditTable());
}
public bool Insert(EquipmentCost eqpct)
{
sql = "INSERT INTO equipment_cost (Equipment_equipmentID, equipCost) VALUES ('" + eqpct.EquipmentId + "' , '" + eqpct.Cost + "')";
return(EditTable());
}
public static ActionResult PDF(string from, string to, string filter, int companyId)
{
var res = ActionResult.NoAction;
var user = HttpContext.Current.Session["User"] as ApplicationUser;
dictionary = HttpContext.Current.Session["Dictionary"] as Dictionary <string, string>;
var company = new Company(companyId);
string path = HttpContext.Current.Request.PhysicalApplicationPath;
if (!path.EndsWith(@"\", StringComparison.OrdinalIgnoreCase))
{
path = string.Format(CultureInfo.InvariantCulture, @"{0}\", path);
}
var fromDate = Tools.DateFromTextYYYYMMDD(from);
var toDate = Tools.DateFromTextYYYYMMDD(to);
var formatedDescription = ToolsPdf.NormalizeFileName(company.Name);
string fileName = string.Format(
CultureInfo.InvariantCulture,
@"{0}_{1}_{2:yyyyMMddhhmmss}.pdf",
dictionary["Item_EquipmentList"],
formatedDescription,
DateTime.Now);
// FONTS
string pathFonts = HttpContext.Current.Request.PhysicalApplicationPath;
if (!path.EndsWith(@"\", StringComparison.OrdinalIgnoreCase))
{
pathFonts = string.Format(CultureInfo.InstalledUICulture, @"{0}\", pathFonts);
}
var headerFont = BaseFont.CreateFont(string.Format(CultureInfo.InvariantCulture, @"{0}fonts\ARIAL.TTF", pathFonts), BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
var arial = BaseFont.CreateFont(string.Format(CultureInfo.InvariantCulture, @"{0}fonts\ARIAL.TTF", pathFonts), BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
var pdfDoc = new iTS.Document(iTS.PageSize.A4.Rotate(), 40, 40, 80, 50);
var writer = iTextSharp.text.pdf.PdfWriter.GetInstance(pdfDoc,
new FileStream(
string.Format(CultureInfo.InvariantCulture, @"{0}Temp\{1}", path, fileName),
FileMode.Create));
writer.PageEvent = new TwoColumnHeaderFooter()
{
CompanyLogo = string.Format(CultureInfo.InvariantCulture, @"{0}\images\logos\{1}.jpg", path, company.Id),
IssusLogo = string.Format(CultureInfo.InvariantCulture, "{0}issus.png", path),
Date = string.Format(CultureInfo.InvariantCulture, "{0:dd/MM/yyyy}", DateTime.Now),
CreatedBy = user.UserName,
CompanyId = company.Id,
CompanyName = company.Name,
Title = dictionary["Item_EquipmentListCosts"].ToUpperInvariant()
};
pdfDoc.Open();
var backgroundColor = new iTS.BaseColor(225, 225, 225);
var rowPair = new iTS.BaseColor(255, 255, 255);
var rowEven = new iTS.BaseColor(240, 240, 240);
var titleTable = new iTSpdf.PdfPTable(1);
titleTable.SetWidths(new float[] { 50f });
var titleCell = new iTSpdf.PdfPCell(new iTS.Phrase(string.Format(CultureInfo.InvariantCulture, "{0} - {1}", dictionary["Item_EquipmentList"], company.Name), ToolsPdf.LayoutFonts.TitleFont))
{
HorizontalAlignment = iTS.Element.ALIGN_CENTER,
Border = iTS.Rectangle.NO_BORDER
};
titleTable.AddCell(titleCell);
var CI = filter.IndexOf("|CI") != -1;
var CE = filter.IndexOf("|CE") != -1;
var VI = filter.IndexOf("|VI") != -1;
var VE = filter.IndexOf("|VE") != -1;
var MI = filter.IndexOf("|MI") != -1;
var ME = filter.IndexOf("|ME") != -1;
var RI = filter.IndexOf("|RI") != -1;
var RE = filter.IndexOf("|RE") != -1;
var AC = filter.IndexOf("|AC") != -1;
var IN = filter.IndexOf("|IN") != -1;
var numberColumns = 2;
numberColumns += CI ? 1 : 0;
numberColumns += CE ? 1 : 0;
numberColumns += VI ? 1 : 0;
numberColumns += VE ? 1 : 0;
numberColumns += MI ? 1 : 0;
numberColumns += ME ? 1 : 0;
numberColumns += RI ? 1 : 0;
numberColumns += RE ? 1 : 0;
var table = new iTSpdf.PdfPTable(numberColumns)
{
WidthPercentage = 100,
HorizontalAlignment = 1,
SpacingBefore = 20f,
SpacingAfter = 30f
};
var widths = new List <float>();
widths.Add(20f + (10 - numberColumns) * 5);
if (CI)
{
widths.Add(5f);
}
if (CE)
{
widths.Add(5f);
}
if (VI)
{
widths.Add(5f);
}
if (VE)
{
widths.Add(5f);
}
if (MI)
{
widths.Add(5f);
}
if (ME)
{
widths.Add(5f);
}
if (RI)
{
widths.Add(5f);
}
if (RE)
{
widths.Add(5f);
}
widths.Add(10f);
table.SetWidths(widths.ToArray());
table.AddCell(ToolsPdf.HeaderCell(dictionary["Item_Equipment_Header_Description"]));
if (CI)
{
table.AddCell(ToolsPdf.HeaderCell(dictionary["Item_Equipment_FilterLabel_Calibration_Int"]));
}
if (CE)
{
table.AddCell(ToolsPdf.HeaderCell(dictionary["Item_Equipment_FilterLabel_Calibration_Ext"]));
}
if (VI)
{
table.AddCell(ToolsPdf.HeaderCell(dictionary["Item_Equipment_FilterLabel_Verification_Int"]));
}
if (VE)
{
table.AddCell(ToolsPdf.HeaderCell(dictionary["Item_Equipment_FilterLabel_Verification_Ext"]));
}
if (MI)
{
table.AddCell(ToolsPdf.HeaderCell(dictionary["Item_Equipment_FilterLabel_Maintenance_Int"]));
}
if (ME)
{
table.AddCell(ToolsPdf.HeaderCell(dictionary["Item_Equipment_FilterLabel_Maintenance_Ext"]));
}
if (RI)
{
table.AddCell(ToolsPdf.HeaderCell(dictionary["Item_Equipment_FilterLabel_Repair_Int"]));
}
if (RE)
{
table.AddCell(ToolsPdf.HeaderCell(dictionary["Item_Equipment_FilterLabel_Repair_Ext"]));
}
table.AddCell(ToolsPdf.HeaderCell(dictionary["Common_Total"]));
int cont = 0;
var data = new List <EquipmentCost>();
decimal totalCI = 0;
decimal totalCE = 0;
decimal totalVI = 0;
decimal totalVE = 0;
decimal totalMI = 0;
decimal totalME = 0;
decimal totalRI = 0;
decimal totalRE = 0;
using (var cmd = new SqlCommand("Equipment_GetCosts2"))
{
using (var cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["cns"].ConnectionString))
{
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(DataParameter.Input("@From", fromDate));
cmd.Parameters.Add(DataParameter.Input("@to", toDate));
cmd.Parameters.Add(DataParameter.Input("@CompanyId", company.Id));
try
{
cmd.Connection.Open();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var newEquipmentCost = new EquipmentCost
{
Id = rdr.GetInt64(8),
Description = rdr.GetString(9),
Active = rdr.GetBoolean(10),
CI = CI ? rdr.GetDecimal(0) : 0,
CE = CE ? rdr.GetDecimal(1) : 0,
VI = VI ? rdr.GetDecimal(2) : 0,
VE = VE ? rdr.GetDecimal(3) : 0,
MI = MI ? rdr.GetDecimal(4) : 0,
ME = ME ? rdr.GetDecimal(5) : 0,
RI = RI ? rdr.GetDecimal(6) : 0,
RE = RE ? rdr.GetDecimal(7) : 0
};
totalCI += newEquipmentCost.CI;
totalCE += newEquipmentCost.CE;
totalVI += newEquipmentCost.VI;
totalVE += newEquipmentCost.VE;
totalMI += newEquipmentCost.MI;
totalME += newEquipmentCost.ME;
totalRI += newEquipmentCost.RI;
totalRE += newEquipmentCost.RE;
data.Add(newEquipmentCost);
}
}
}
finally
{
if (cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
}
}
}
}
//------ CRITERIA
var criteriatable = new iTSpdf.PdfPTable(2)
{
WidthPercentage = 100
};
criteriatable.SetWidths(new float[] { 8f, 50f });
string periodText = string.Empty;
string operativaText = string.Empty;
if (string.IsNullOrEmpty(from) && string.IsNullOrEmpty(to))
{
periodText = dictionary["Common_PeriodAll"];
}
else if (!string.IsNullOrEmpty(from) && !string.IsNullOrEmpty(to))
{
periodText = string.Format(
@"{0} {1:dd/MM/yyyy} {2} {3:dd/MM/yyyy}",
dictionary["Common_From"],
Tools.DateFromTextYYYYMMDD(from),
dictionary["Common_To"],
Tools.DateFromTextYYYYMMDD(to));
}
else if (!string.IsNullOrEmpty(from))
{
periodText = string.Format(
@"{0} {1:dd/MM/yyyy}",
dictionary["Common_From"],
Tools.DateFromTextYYYYMMDD(from));
}
else
{
periodText = string.Format(
@"{0} {1:dd/MM/yyyy}",
dictionary["Common_To"],
Tools.DateFromTextYYYYMMDD(to));
}
bool first = true;
if (CI)
{
first = false;
operativaText = dictionary["Item_Equipment_FilterLabel_Calibration_Int"];
}
if (CE)
{
if (!first)
{
operativaText += ", ";
}
first = false;
operativaText += dictionary["Item_Equipment_FilterLabel_Calibration_Ext"];
}
if (VI)
{
if (!first)
{
operativaText += ", ";
}
first = false;
operativaText += dictionary["Item_Equipment_FilterLabel_Verification_Int"];
}
if (VE)
{
if (!first)
{
operativaText += ", ";
}
first = false;
operativaText += dictionary["Item_Equipment_FilterLabel_Verification_Ext"];
}
if (MI)
{
if (!first)
{
operativaText += ", ";
}
first = false;
operativaText += dictionary["Item_Equipment_FilterLabel_Maintenance_Int"];
}
if (ME)
{
if (!first)
{
operativaText += ", ";
}
first = false;
operativaText += dictionary["Item_Equipment_FilterLabel_Maintenance_Ext"];
}
if (RI)
{
if (!first)
{
operativaText += ", ";
}
first = false;
operativaText += dictionary["Item_Equipment_FilterLabel_Repair_Int"];
}
if (RE)
{
if (!first)
{
operativaText += ", ";
}
first = false;
operativaText += dictionary["Item_Equipment_FilterLabel_Repair_Ext"];
}
ToolsPdf.AddCriteria(criteriatable, dictionary["Item_Equipment_List_Filter_ShowByOperation"], operativaText);
ToolsPdf.AddCriteria(criteriatable, dictionary["Item_Equipment_List_Filter_ShowByPeriod"], periodText);
bool pair = false;
decimal total = 0;
int border = 0;
foreach (EquipmentCost equipment in data)
{
if (equipment.Total == 0)
{
continue;
}
total += equipment.Total;
var lineBackground = pair ? rowEven : rowPair;
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(equipment.Description, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f
});
if (CI)
{
string CIText = string.Format("{0:#,##0.00}", equipment.CI);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(CIText, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (CE)
{
string CEText = string.Format("{0:#,##0.00}", equipment.CE);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(CEText, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (VI)
{
string VIText = string.Format("{0:#,##0.00}", equipment.VI);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(VIText, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (VE)
{
string VEText = string.Format("{0:#,##0.00}", equipment.VE);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(VEText, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (MI)
{
string MIText = string.Format("{0:#,##0.00}", equipment.MI);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(MIText, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (ME)
{
string METext = string.Format("{0:#,##0.00}", equipment.ME);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(METext, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (RI)
{
string RIText = string.Format("{0:#,##0.00}", equipment.RI);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(RIText, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (RE)
{
string REText = string.Format("{0:#,##0.00}", equipment.RE);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(REText, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
string TotalText = string.Format("{0:#,##0.00}", equipment.Total);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(TotalText, ToolsPdf.LayoutFonts.Times))
{
Border = border,
BackgroundColor = lineBackground,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
cont++;
}
string totalRegistros = string.Format(
CultureInfo.InvariantCulture,
@"{0}: {1}",
dictionary["Common_RegisterCount"],
cont);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalRegistros, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f
});
/*table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(dictionary["Common_Total"].ToUpperInvariant(), ToolsPdf.LayoutFonts.Times))
* {
* Border = iTS.Rectangle.TOP_BORDER,
* BackgroundColor = rowEven,
* Padding = 6f,
* PaddingTop = 4f,
* HorizontalAlignment = 2
* });*/
if (CI)
{
string totalTextCI = string.Format("{0:#,##0.00}", totalCI);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalTextCI, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (CE)
{
string totalTextCE = string.Format("{0:#,##0.00}", totalCE);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalTextCE, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (VI)
{
string totalTextVI = string.Format("{0:#,##0.00}", totalVI);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalTextVI, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (VE)
{
string totalTextVE = string.Format("{0:#,##0.00}", totalVE);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalTextVE, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (MI)
{
string totalTextMI = string.Format("{0:#,##0.00}", totalMI);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalTextMI, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (ME)
{
string totalTextME = string.Format("{0:#,##0.00}", totalME);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalTextME, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (RI)
{
string totalTextRI = string.Format("{0:#,##0.00}", totalRI);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalTextRI, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
if (RE)
{
string totalTextRE = string.Format("{0:#,##0.00}", totalRE);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalTextRE, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
}
string totalText = string.Format("{0:#,##0.00}", total);
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(totalText, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Padding = 6f,
PaddingTop = 4f,
HorizontalAlignment = 2
});
table.AddCell(new iTSpdf.PdfPCell(new iTS.Phrase(string.Empty, ToolsPdf.LayoutFonts.Times))
{
Border = iTS.Rectangle.TOP_BORDER,
BackgroundColor = rowEven,
Colspan = 2
});
pdfDoc.Add(criteriatable);
pdfDoc.Add(table);
pdfDoc.CloseDocument();
res.SetSuccess(string.Format(CultureInfo.InvariantCulture, @"{0}Temp/{1}", ConfigurationManager.AppSettings["siteUrl"], fileName));
return(res);
}
