protected void btOrder_Check_Click(object sender, EventArgs e)
{
FAbonentDO faDO = new FAbonentDO();
FOrderDO foDO = new FOrderDO();
FOrder fo;
FAbonent fa;
UniversalEntity ue = new UniversalEntity();
int id = Convert.ToInt32(hfODID.Value);
ue = faDO.RetrieveByOrderID(id);
FOrderDetailsDO fodDO = new FOrderDetailsDO();
//FOrderDetails fod;
double sum = 0;
if (ue.Count > 0)
{
fa = (FAbonent)ue[0];
ue = foDO.RetrieveFOrderById(id);
if (ue.Count > 0)
{
fo = (FOrder)ue[0];
ue = fodDO.RetrieveFOrderDetailsByOrderID(id);
foreach (FOrderDetails fod in ue)
{
sum += fod.Price;
}
using (DocX document = DocX.Load(Request.MapPath("~\\Templates/order_check.docx")))
{
//DocXExtender.ReplaceFormatedText(document, "DDD", "дата");
document.ReplaceText("FIO", fa.Surname + " " + fa.FirstName + " " + fa.LastName, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("ADDRESS", fa.Address, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("PNONE", fa.Phone, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("DATE", DateTime.Now.ToString("dd MMMM yyyy"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("NUMBER", fo.Prefix + fo.ID.ToString(), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("TYPE", fo.ActionType, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("SUM", sum.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("VAT", Utilities.GetVAT(sum).ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("ALL", (sum + Utilities.GetVAT(sum)).ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
//VODOMER
document.SaveAs(Request.MapPath("~\\Templates/outorder_check.docx"));
litScript.Text = "<iframe style=\"display:none;\" src=\"../GetDocument.ashx?ordercheck=1\"></iframe>";
}
}
}
}
//генерация квитнации
protected void btPay_Click(object sender, EventArgs e)
{
FAbonentDO faDO = new FAbonentDO();
FOrderDO foDO = new FOrderDO();
VodomerType vt = new VodomerType();
FOrder fo;
FAbonent fa;
UniversalEntity ue = new UniversalEntity();
int id = Convert.ToInt32(hfODID.Value);
ue = faDO.RetrieveByOrderID(id);
FOrderDetailsDO fodDO = new FOrderDetailsDO();
//FOrderDetails fod;
double sum = 0; //гривневая цена
double getvat = 0; //гривневый ндс
double uafin = 0; //гривневая итоговая сумма
double sumrub = 0; //рублевая цена
double getvatrub = 0; //рублевый ндс
double finish = 0; //рублевая итоговая сумма
if (ue.Count > 0)
{
fa = (FAbonent)ue[0];
ue = foDO.RetrieveFOrderById(id);
if (ue.Count > 0)
{
fo = (FOrder)ue[0];
ue = fodDO.RetrieveFOrderDetailsByOrderID(id);
foreach (FOrderDetails fod in ue)
{
//гривневый счет
sum += fod.Price;
getvat = sum * 0.2;
getvat = Math.Round(getvat, 2);
uafin = (sum + getvat);
//рублевый счет
sumrub += fod.Price;
getvatrub = sumrub * 0.2;
getvatrub = Math.Round(getvatrub, 2);
finish = (sumrub + getvatrub) + (sumrub + getvatrub);
}
using (DocX document = DocX.Load(Request.MapPath("~\\Templates/kvit.docx")))
{
//DocXExtender.ReplaceFormatedText(document, "DDD", "дата");
document.ReplaceText("FIO", fa.Surname + " " + fa.FirstName + " " + fa.LastName, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("ADDRESS", fa.Address, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("PNONE", fa.Phone, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("DATE", DateTime.Now.ToString("dd MMMM yyyy"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("NUMBER", fo.Prefix + fo.ID.ToString(), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("VIEW", fo.ActionType, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("DIAMETR", vt.Diameter.ToString(), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("SUM", sum.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("VAT", getvat.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("ALL", uafin.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("CENA", sumrub.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("NDS", getvatrub.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("VSEGO", finish.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
//VODOMER
document.SaveAs(Request.MapPath("~\\Templates/kvitfa.docx"));
litScript.Text = "<iframe style=\"display:none;\" src=\"../GetDocument.ashx?pay=Private\"></iframe>";
}
}
}
}
public static void GenerateFActSpecial(int id, int ULocationID)
{
UniversalEntity ue = new UniversalEntity();
string header = string.Empty;
string actNumber = string.Empty;
string actNumber2 = string.Empty;
FAbonentDO faDO = new FAbonentDO();
ue = faDO.RetrieveByOrderID(id);
FAbonent fa = new FAbonent();
if (ue.Count > 0)
{
fa = (FAbonent)ue[0];
header += fa.Surname + " " + fa.FirstName + " " + fa.LastName + " \nадрес: " + fa.Address + "\nтел.: " + fa.Phone;
}
FOrder fo = new FOrder();
FOrderDO foDO = new FOrderDO();
FOrderDetailsDO fodDO = new FOrderDetailsDO();
ue = foDO.RetrieveFOrderById(id);
if (ue.Count > 0)
{
fo = (FOrder)ue[0];
actNumber = " г. Донецк АКТ № " + fo.Prefix + fo.ID + " от ____________________" /*+ RuDateAndMoneyConverter.DateToTextLong(fo.DateIn)*/ + " г.";
actNumber2 = " по договору (письму) № " + fo.Prefix + fo.ID + " от ____________________" + /*RuDateAndMoneyConverter.DateToTextLong(fo.DateIn) +*/ " г.";
}
ue = fodDO.RetrieveFOrderDetailsByOrderID(id);
InitializeWorkbookFActSpecial(); //InitializeWorkbook();
ISheet sheet1 = hssfworkbook.GetSheet("s");
//create cell on rows, since rows do already exist,it's not necessary to create rows again.
sheet1.GetRow(2).GetCell(3).SetCellValue(header);
sheet1.GetRow(32).GetCell(3).SetCellValue(header);
sheet1.GetRow(9).GetCell(0).SetCellValue(actNumber);
sheet1.GetRow(39).GetCell(0).SetCellValue(actNumber);
sheet1.GetRow(11).GetCell(0).SetCellValue(actNumber2);
//sheet1.GetRow(41).GetCell(0).SetCellValue(actNumber2);
int rowC = 44;
int row = 14;
double sum = 0;
int count = ue.Count;
if (count <= 5)
{
CustomRetrieverDO crDO = new CustomRetrieverDO();
ue = crDO.RetrieveFActByOrderID5Low(id);
if (ue.Count > 0)
{
for (int i = row; i < ue.Count + row; i++)
{
ArrayList al = (ArrayList)ue[i - row];
//FOrderDetailsAct u = (FOrderDetailsAct)fodal[i - row];
sheet1.GetRow(i).GetCell(0).SetCellValue(i - row + 1);
sheet1.GetRow(i).GetCell(1).SetCellValue(string.Format("Определения метрологических характеристик водомера D{0}, {1}, {2}", al[0].ToString(), al[1].ToString(), al[2].ToString()));
sheet1.GetRow(i).GetCell(2).SetCellValue("калькуляция");
sheet1.GetRow(i).GetCell(3).SetCellValue("шт.");
sheet1.GetRow(i).GetCell(4).SetCellValue(al[4].ToString());
sheet1.GetRow(i).GetCell(5).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sheet1.GetRow(i).GetCell(6).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sum += Convert.ToDouble(al[3]);
rowC++;
}
}
}
else
{
CustomRetrieverDO crDO = new CustomRetrieverDO();
ue = crDO.RetrieveFActByOrderID5High(id);
if (ue.Count > 0)
{
for (int i = row; i < ue.Count + row; i++)
{
ArrayList al = (ArrayList)ue[i - row];
//FOrderDetailsAct u = (FOrderDetailsAct)fodal[i - row];
sheet1.GetRow(i).GetCell(0).SetCellValue(i - row + 1);
sheet1.GetRow(i).GetCell(1).SetCellValue(string.Format("Определения метрологических характеристик водомера D{0}", al[0].ToString()));
sheet1.GetRow(i).GetCell(2).SetCellValue("калькуляция");
sheet1.GetRow(i).GetCell(3).SetCellValue("шт.");
sheet1.GetRow(i).GetCell(4).SetCellValue(al[3].ToString());
sheet1.GetRow(i).GetCell(5).SetCellValue(Convert.ToDouble(al[1]).ToString("0.00"));
sheet1.GetRow(i).GetCell(6).SetCellValue(Convert.ToDouble(al[2]).ToString("0.00"));
sum += Convert.ToDouble(al[2]);
rowC++;
}
}
}
sheet1.GetRow(19).GetCell(6).SetCellValue(sum.ToString("0.00"));
sheet1.GetRow(20).GetCell(6).SetCellValue((sum / 100 * 20).ToString("0.00"));
sheet1.GetRow(21).GetCell(6).SetCellValue((sum + (sum / 100 * 20)).ToString("0.00"));
string tmp = string.Empty;
tmp = sheet1.GetRow(29).GetCell(GetLetterNumber("c")).StringCellValue;
string tmp1 = string.Empty;
tmp1 = sheet1.GetRow(41).GetCell(GetLetterNumber("a")).StringCellValue;
CustomRetrieverDO crdo = new CustomRetrieverDO();
ue = crdo.RetrieveUserLocation(ULocationID);
if (ue.Count > 0)
{
tmp1 = tmp1.Replace("257-42-61", (ue[0] as ArrayList)[2].ToString());
}
//tmp1 = tmp1.Replace("257-42-61", "");
tmp = tmp.Replace("FIO", fa.Surname + " " + fa.FirstName + " " + fa.LastName);
tmp = tmp.Replace("ADDRESS", fa.Address);
tmp = tmp.Replace("PNONE", fa.Phone);
tmp = tmp.Replace("DATE", DateTime.Now.ToString("dd MMMM yyyy"));
tmp = tmp.Replace("NUMBER", fo.Prefix + fo.ID.ToString());
tmp = tmp.Replace("TYPE", fo.ActionType);
//tmp = tmp.Replace("SUM", sum.ToString("0.00"));
//tmp = tmp.Replace("VAT", Utilities.GetVAT(sum).ToString("0.00"));
tmp = tmp.Replace("ALL", (sum + Utilities.GetVAT(sum)).ToString("0.00"));
sheet1.GetRow(41).GetCell(GetLetterNumber("a")).SetCellValue(tmp1);
sheet1.GetRow(29).GetCell(GetLetterNumber("c")).SetCellValue(tmp);
sheet1.GetRow(41).GetCell(GetLetterNumber("c")).SetCellValue(tmp);
/*sheet1.GetRow(49).GetCell(6).SetCellValue(sum.ToString("0.00"));
* sheet1.GetRow(50).GetCell(6).SetCellValue((sum / 100 * 20).ToString("0.00"));
* sheet1.GetRow(51).GetCell(6).SetCellValue((sum + (sum / 100 * 20)).ToString("0.00"));*/
//Force excel to recalculate all the formula while open
sheet1.ForceFormulaRecalculation = true;
WriteToFileFSpecial();
}
protected void btOrder_Check_Click(object sender, EventArgs e)
{
NewFAbonentDO faDO = new NewFAbonentDO();
FOrderDO foDO = new FOrderDO();
FOrder fo;
NewFAbonent fa;
UniversalEntity ue = new UniversalEntity();
int id = Convert.ToInt32(hfODID.Value);
ue = faDO.RetrieveByOrderID(id);
FOrderDetailsDO fodDO = new FOrderDetailsDO();
//FOrderDetails fod;
double sum = 0; //гривневая цена
double getvat = 0; //гривневый ндс
double uafin = 0; //гривневая итоговая сумма
double sumrub = 0; //рублевая цена
double getvatrub = 0; //рублвый ндс
double finish = 0; //рублевая итоговая сумма
//RetrieveFOrderByID (данные из FOrder + данные из NewFAbonent)
if (ue.Count > 0)
{
fa = (NewFAbonent)ue[0];
ue = foDO.RetrieveFOrderById(id);
//RetrieveFOrderByID (данные из FOrder + данные из NewFAbonent)
if (ue.Count > 0)
{
fo = (FOrder)ue[0];
ue = fodDO.RetrieveFOrderDetailsByOrderID(id);
//цикл с RetrieveFOrderDetailsByOrderID (данные из FOrderDetails + данные из FOrder)
foreach (FOrderDetails fod in ue)
{
//гривневый счет
sum += fod.Price;
getvat = sum * 0.2;
uafin = (sum + getvat);
//рублевый счет
sumrub += fod.Price;
getvatrub = sumrub * 0.2;
finish = (sumrub + getvatrub) + (sumrub + getvatrub);
}
using (DocX document = DocX.Load(Request.MapPath("~\\Templates/order_check.docx")))
{
//DocXExtender.ReplaceFormatedText(document, "DDD", "дата");
document.ReplaceText("FIO", fa.Surname + " " + fa.FirstName + " " + fa.LastName, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("ADDRESS", fa.Address, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("PNONE", fa.Phone, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("DATE", DateTime.Now.ToString("dd MMMM yyyy"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("NUMBER", fo.Prefix + fo.ID.ToString(), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("TYPE", fo.ActionType, false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
// document.ReplaceText("SUM", sum.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
// document.ReplaceText("VAT", Utilities.GetVAT(sum).ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("ALL", uafin.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
// document.ReplaceText("CENA", sumrub.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
// document.ReplaceText("NDS", (Utilities.GetVATRub(sumrub)).ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
document.ReplaceText("VSEGO", finish.ToString("0.00"), false, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
//VODOMER
document.SaveAs(Request.MapPath("~\\Templates/outorder_check.docx"));
litScript.Text = "<iframe style=\"display:none;\" src=\"../GetDocument.ashx?ordercheck=1\"></iframe>";
}
}
}
}
public static void GenerateFAct(int id)
{
UniversalEntity ue = new UniversalEntity();
string header = string.Empty;
string actNumber = string.Empty;
string actNumber2 = string.Empty;
FAbonentDO faDO = new FAbonentDO();
ue = faDO.RetrieveByOrderID(id);
if (ue.Count > 0)
{
FAbonent fa = (FAbonent)ue[0];
header += fa.Surname + " " + fa.FirstName + " " + fa.LastName + " \nадрес: " + fa.Address + "\nтел.: " + fa.Phone;
}
FOrder fo = new FOrder();
FOrderDO foDO = new FOrderDO();
FOrderDetailsDO fodDO = new FOrderDetailsDO();
ue = foDO.RetrieveFOrderById(id);
if (ue.Count > 0)
{
fo = (FOrder)ue[0];
actNumber = " г. Донецк АКТ № " + fo.Prefix + fo.ID + " от " + RuDateAndMoneyConverter.DateToTextLong(fo.DateIn) + " г.";
actNumber2 = " по договору (письму) № " + fo.Prefix + fo.ID + " от " + RuDateAndMoneyConverter.DateToTextLong(fo.DateIn) + " г.";
}
ue = fodDO.RetrieveFOrderDetailsByOrderID(id);
InitializeWorkbookFAct(); //InitializeWorkbook();
ISheet sheet1 = hssfworkbook.GetSheet("s");
//create cell on rows, since rows do already exist,it's not necessary to create rows again.
sheet1.GetRow(2).GetCell(3).SetCellValue(header);
sheet1.GetRow(32).GetCell(3).SetCellValue(header);
sheet1.GetRow(9).GetCell(0).SetCellValue(actNumber);
sheet1.GetRow(39).GetCell(0).SetCellValue(actNumber);
sheet1.GetRow(11).GetCell(0).SetCellValue(actNumber2);
sheet1.GetRow(41).GetCell(0).SetCellValue(actNumber2);
int rowC = 44;
int row = 14;
double sum = 0;
int count = ue.Count;
if (count <= 5)
{
CustomRetrieverDO crDO = new CustomRetrieverDO();
ue = crDO.RetrieveFActByOrderID5Low(id);
if (ue.Count > 0)
{
for (int i = row; i < ue.Count + row; i++)
{
ArrayList al = (ArrayList)ue[i - row];
//FOrderDetailsAct u = (FOrderDetailsAct)fodal[i - row];
sheet1.GetRow(i).GetCell(0).SetCellValue(i - row + 1);
sheet1.GetRow(i).GetCell(1).SetCellValue(string.Format("Определения метрологических характеристик водомера D{0}, {1}, {2}", al[0].ToString(), al[1].ToString(), al[2].ToString()));
sheet1.GetRow(i).GetCell(2).SetCellValue("калькуляция");
sheet1.GetRow(i).GetCell(3).SetCellValue("шт.");
sheet1.GetRow(i).GetCell(4).SetCellValue(al[4].ToString());
sheet1.GetRow(i).GetCell(5).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sheet1.GetRow(i).GetCell(6).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sheet1.GetRow(rowC).GetCell(0).SetCellValue(i - row + 1);
sheet1.GetRow(rowC).GetCell(1).SetCellValue(string.Format("Определения метрологических характеристик водомера D{0}, {1}, {2}", al[0].ToString(), al[1].ToString(), al[2].ToString()));
sheet1.GetRow(rowC).GetCell(2).SetCellValue("калькуляция");
sheet1.GetRow(rowC).GetCell(3).SetCellValue("шт.");
sheet1.GetRow(rowC).GetCell(4).SetCellValue(al[4].ToString());
sheet1.GetRow(rowC).GetCell(5).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sheet1.GetRow(rowC).GetCell(6).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sum += Convert.ToDouble(al[3]);
rowC++;
}
}
}
else
{
CustomRetrieverDO crDO = new CustomRetrieverDO();
ue = crDO.RetrieveFActByOrderID5High(id);
if (ue.Count > 0)
{
for (int i = row; i < ue.Count + row; i++)
{
ArrayList al = (ArrayList)ue[i - row];
//FOrderDetailsAct u = (FOrderDetailsAct)fodal[i - row];
sheet1.GetRow(i).GetCell(0).SetCellValue(i - row + 1);
sheet1.GetRow(i).GetCell(1).SetCellValue(string.Format("Определения метрологических характеристик водомера D{0}", al[0].ToString()));
sheet1.GetRow(i).GetCell(2).SetCellValue("калькуляция");
sheet1.GetRow(i).GetCell(3).SetCellValue("шт.");
sheet1.GetRow(i).GetCell(4).SetCellValue(al[3].ToString());
sheet1.GetRow(i).GetCell(5).SetCellValue(Convert.ToDouble(al[1]).ToString("0.00"));
sheet1.GetRow(i).GetCell(6).SetCellValue(Convert.ToDouble(al[2]).ToString("0.00"));
sheet1.GetRow(rowC).GetCell(0).SetCellValue(i - row + 1);
sheet1.GetRow(rowC).GetCell(1).SetCellValue(string.Format("Определения метрологических характеристик водомера D{0}", al[0].ToString()));
sheet1.GetRow(rowC).GetCell(2).SetCellValue("калькуляция");
sheet1.GetRow(rowC).GetCell(3).SetCellValue("шт.");
sheet1.GetRow(rowC).GetCell(4).SetCellValue(al[3].ToString());
sheet1.GetRow(rowC).GetCell(5).SetCellValue(Convert.ToDouble(al[1]).ToString("0.00"));
sheet1.GetRow(rowC).GetCell(6).SetCellValue(Convert.ToDouble(al[2]).ToString("0.00"));
sum += Convert.ToDouble(al[2]);
rowC++;
}
}
}
sheet1.GetRow(19).GetCell(6).SetCellValue(sum.ToString("0.00"));
sheet1.GetRow(20).GetCell(6).SetCellValue((sum / 100 * 20).ToString("0.00"));
sheet1.GetRow(21).GetCell(6).SetCellValue((sum + (sum / 100 * 20)).ToString("0.00"));
sheet1.GetRow(49).GetCell(6).SetCellValue(sum.ToString("0.00"));
sheet1.GetRow(50).GetCell(6).SetCellValue((sum / 100 * 20).ToString("0.00"));
sheet1.GetRow(51).GetCell(6).SetCellValue((sum + (sum / 100 * 20)).ToString("0.00"));
//Force excel to recalculate all the formula while open
sheet1.ForceFormulaRecalculation = true;
WriteToFileF();
}