private void LoadTypeDevice()
{
CustomRetrieverDO crdo = new CustomRetrieverDO();
List <Book> lbook = crdo.RetrieveWPTypeDevice();
ddlTypeDevice.DataSource = lbook;
ddlTypeDevice.DataTextField = "Title";
ddlTypeDevice.DataValueField = "ID";
ddlTypeDevice.DataBind();
}
private void LoadGrop()
{
ddlGroup.Items.Clear();
CustomRetrieverDO crDO = new CustomRetrieverDO();
UniversalEntity ue = new UniversalEntity();
ue = crDO.RetrieveGroups();
foreach (ArrayList al in ue)
{
ddlGroup.Items.Add(new ListItem(al[1].ToString(), al[0].ToString()));
}
}
void BindDistricts()
{
ddsDistrict.Items.Clear();
CustomRetrieverDO cdo = new CustomRetrieverDO();
UniversalEntity ue = new UniversalEntity();
ue = cdo.RetrieveDistricts();
ArrayList al = new ArrayList();
for (int i = 0; i < ue.Count; i++)
{
al = (ArrayList)ue[i];
ddsDistrict.Items.Add(new ListItem(al[1].ToString(), al[0].ToString()));
}
}
private void LoadTypeDevice()
{
CustomRetrieverDO crdo = new CustomRetrieverDO();
List <Book> lbook = crdo.RetrieveWPTypeDevice();
radddlTypeDevice.DataSource = lbook;
radddlTypeDevice.DataTextField = "Title";
radddlTypeDevice.DataValueField = "ID";
radddlTypeDevice.DataBind();
/*foreach (Book b in lbook)
* {
* ddlTypeDevice.Items.Add(new ListItem(b.Title, b.ID.ToString()));
* radddlTypeDevice.Items.Add((new ListItem(b.Title, b.ID.ToString()));
* }*/
}
void BindDiameter()
{
ddlDiametr.Items.Clear();
CustomRetrieverDO crdo = new CustomRetrieverDO();
UniversalEntity ue = new UniversalEntity();
ue = crdo.RetrieveDiameters();
ddlDiametr.Items.Add(new ListItem("Диаметр", "-1"));
ArrayList al;
for (int i = 0; i < ue.Count; i++)
{
al = (ArrayList)ue[i];
ddlDiametr.Items.Add(new ListItem(al[0].ToString(), al[0].ToString()));
}
}
protected void btSet_Click(object sender, EventArgs e)
{
CustomRetrieverDO crdo = new CustomRetrieverDO();
UniversalEntity ue = new UniversalEntity();
ue = crdo.GetRateByDateAndWP(Utilities.ConvertToInt(radWP.SelectedValue), Utilities.ConvertToInt(radddlUnits.SelectedValue), dFrom.SelectedDate.Value, dTo.SelectedDate.Value);
/*Random r = new Random();
*
* List<Double> c = new List<double>();
* c.Add(Math.Round((r.NextDouble() * 100)));
* c.Add(Math.Round((r.NextDouble() * 100)));
* c.Add(Math.Round((r.NextDouble() * 100)));
* c.Add(Math.Round((r.NextDouble() * 100)));
* c.Add(Math.Round((r.NextDouble() * 100)));
* c.Add(Math.Round((r.NextDouble() * 100)));
* */
AddNewSeries(ue);
}
protected void Page_Load(object sender, EventArgs e)
{
List <AccountParser> lst = new List <AccountParser>();
FileStream stream = File.Open(@"E:\import.xls", FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
// DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = false;
DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
string tmp = string.Empty;
string abonent = string.Empty;
string district = string.Empty;
string district1 = string.Empty;
CustomRetrieverDO crdo = new CustomRetrieverDO();
UniversalEntity ue = new UniversalEntity();
int i = 0;
while (excelReader.Read())
{
//Response.Write(string.Format("{0}, {1}",excelReader.GetString(1),excelReader.GetString(7))) ;
/*try
* {
* Response.Write("6 "+excelReader.GetValue(6).ToString()+"<br/>");
* }
* catch (Exception ex)
* {
* }*/
try
{
if (excelReader.GetValue(0).ToString().Contains("Счет"))
{
tmp = excelReader.GetValue(0).ToString();
tmp = tmp.Replace("Счет-фактура ", "");
tmp = tmp.Remove(tmp.Length - 11, 11);
tmp = tmp.Replace(",", "");
tmp = tmp.Replace(".", "");
tmp = Regex.Replace(tmp, "\\D", "");
ue = crdo.RetrieveUserLocationByFOrder(Utilities.ConvertToInt(tmp));
if (ue.Count > 0)
{
district = ue[0].ToString();
}
else
{
district = "Не найден";
}
ue = crdo.RetrieveUserLocationBySurname(abonent);
if (ue.Count > 0)
{
district1 = ue[0].ToString();
}
else
{
district1 = "Не найден";
}
AccountParser ap = new AccountParser();
ap.n = i;
ap.d1 = district;
ap.d2 = district1;
lst.Add(ap);
//Response.Write(i.ToString()+ " " +tmp + " " + abonent + " " + district + " " + district + "<br/>");
}
else
{
abonent = excelReader.GetValue(0).ToString();
abonent = abonent.Split(' ')[1];
abonent = abonent.Replace(",", "");
abonent = abonent.Replace(".", "");
}
i++;
}
catch (Exception ex)
{
}
//excelReader.GetInt32(0);
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
ExportToExcel.GenerateAccountParser(lst);
}
public static void GenerateBill(int id)
{
InitializeWorkbookBill();
ISheet sheet1 = hssfworkbook.GetSheet("s");
UniversalEntity ue = new UniversalEntity();
//UniversalEntity uev = new UniversalEntity();
UAbonent ua = new UAbonent();
UAbonentDO uado = new UAbonentDO();
UOrder uo = new UOrder();
UOrderDO uodo = new UOrderDO();
UOrderDetailsDO uodDO = new UOrderDetailsDO();
ue = uado.RetrieveByOrderID(id);
string header = string.Empty;
string actNumber = string.Empty;
if (ue.Count > 0)
{
ua = (UAbonent)ue[0];
header += ua.Title + " \nюр адрес: " + ua.Address + " \n№ св. пл. НДС: " + ua.VATPay + " \n№ ИНН: " + ua.INN + " \nтел.: " + ua.Phone;
}
ue = uodo.RetrieveUOrderById(id);
if (ue.Count > 0)
{
uo = (UOrder)ue[0];
sheet1.GetRow(4).GetCell(GetLetterNumber("aj")).SetCellValue(uo.ID);
sheet1.GetRow(9).GetCell(GetLetterNumber("ai")).SetCellValue(RuDateAndMoneyConverter.DateToTextLongUA(uo.DateIn) + "р.");
}
ue = uodDO.RetrieveUOrderDetailsByOrderID(id);
sheet1.GetRow(16).GetCell(6).SetCellValue(header);
int count = ue.Count;
double sum = 0;
int row = 20;
if (count <= 5)
{
CustomRetrieverDO crDO = new CustomRetrieverDO();
ue = crDO.RetrieveUActByOrderID5Low(id);
if (ue.Count > 0)
{
for (int i = row; i < ue.Count + row; i++)
{
ArrayList al = (ArrayList)ue[i - row];
sheet1.GetRow(i).GetCell(GetLetterNumber("a")).SetCellValue(string.Format("Определения метрологических характеристик водомера D{0}, {1}, {2}", al[0].ToString(), al[1].ToString(), al[2].ToString()));
sheet1.GetRow(i).GetCell(GetLetterNumber("ad")).SetCellValue("шт.");
sheet1.GetRow(i).GetCell(GetLetterNumber("aj")).SetCellValue(al[4].ToString());
sheet1.GetRow(i).GetCell(GetLetterNumber("ap")).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sheet1.GetRow(i).GetCell(GetLetterNumber("av")).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sum += Convert.ToDouble(al[3]);
}
}
}
else
{
CustomRetrieverDO crDO = new CustomRetrieverDO();
ue = crDO.RetrieveUActByOrderID5High(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(GetLetterNumber("a")).SetCellValue(string.Format("Определения метрологических характеристик водомера D{0}", al[0].ToString()));
sheet1.GetRow(i).GetCell(GetLetterNumber("ad")).SetCellValue("шт.");
sheet1.GetRow(i).GetCell(GetLetterNumber("aj")).SetCellValue(al[3].ToString());
sheet1.GetRow(i).GetCell(GetLetterNumber("ap")).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sheet1.GetRow(i).GetCell(GetLetterNumber("av")).SetCellValue(Convert.ToDouble(al[3]).ToString("0.00"));
sum += Convert.ToDouble(al[2]);
}
}
}
/*int row = 20;
* double sum = 0;
*
*
*
*
*
* if (ue.Count > 0)
* {
* for (int i = row; i < ue.Count + row; i++)
* {
* UOrderDetails u = (UOrderDetails)ue[i - row];
*
* sheet1.GetRow(i).GetCell(GetLetterNumber("a")).SetCellValue("Определения метрологических характеристик водомера ");
* sheet1.GetRow(i).GetCell(GetLetterNumber("ad")).SetCellValue("шт.");
* sheet1.GetRow(i).GetCell(GetLetterNumber("aj")).SetCellValue("1");
* sheet1.GetRow(i).GetCell(GetLetterNumber("ap")).SetCellValue(u.Price.ToString("0.00"));
* sheet1.GetRow(i).GetCell(GetLetterNumber("av")).SetCellValue(u.Price.ToString("0.00"));
* sum += u.Price;
* }
* }*/
sheet1.GetRow(31).GetCell(GetLetterNumber("av")).SetCellValue(sum.ToString("0.00"));
sheet1.GetRow(32).GetCell(GetLetterNumber("av")).SetCellValue(Utilities.GetVAT(sum).ToString("0.00"));
sheet1.GetRow(33).GetCell(GetLetterNumber("av")).SetCellValue((sum + Utilities.GetVAT(sum)).ToString("0.00"));
sheet1.GetRow(35).GetCell(GetLetterNumber("a")).SetCellValue("Загальна сума, що підлягає оплаті: " + UADateAndMoneyConverter.CurrencyToTxt((sum + Utilities.GetVAT(sum)), true));
//Загальна сума, що підлягає оплаті
//Force excel to recalculate all the formula while open
sheet1.ForceFormulaRecalculation = true;
WriteToFileB();
}
public static void GenerateUAct(int id)
{
UniversalEntity ue = new UniversalEntity();
UAbonent ua = new UAbonent();
UAbonentDO uado = new UAbonentDO();
UOrder uo = new UOrder();
UOrderDO uodo = new UOrderDO();
UOrderDetailsDO uodDO = new UOrderDetailsDO();
ue = uado.RetrieveByOrderID(id);
string header = string.Empty;
string actNumber = string.Empty;
if (ue.Count > 0)
{
ua = (UAbonent)ue[0];
header += ua.Title + " \nюр адрес: " + ua.Address + " \n№ св. пл. НДС: " + ua.VATPay + " \n№ ИНН: " + ua.INN + " \nтел.: " + ua.Phone;
}
ue = uodo.RetrieveUOrderById(id);
if (ue.Count > 0)
{
uo = (UOrder)ue[0];
if (uo.DateOut.HasValue)
{
actNumber = " г. Донецк АКТ № " + uo.ID + " от ____________________" + DateTime.Now.Year.ToString() + " г.";
}
else
{
actNumber = " г. Донецк АКТ № " + uo.ID + " от ____________________" + DateTime.Now.Year.ToString() + " г.";
}
}
ue = uodDO.RetrieveUOrderDetailsByOrderID(id);
InitializeWorkbook();
ISheet sheet1 = hssfworkbook.GetSheet("s");
sheet1.GetRow(11).GetCell(0).SetCellValue(" по договору (письму) № " + uo.ID + " от ____________________" + DateTime.Now.Year.ToString() + " г.");
sheet1.GetRow(41).GetCell(0).SetCellValue(" по договору (письму) № " + uo.ID + " от ____________________" + DateTime.Now.Year.ToString() + " г.");
//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);
int rowC = 44;
int row = 14;
double sum = 0;
int count = ue.Count;
if (count <= 5)
{
CustomRetrieverDO crDO = new CustomRetrieverDO();
ue = crDO.RetrieveUActByOrderID5Low(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.RetrieveUActByOrderID5High(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++;
}
}
}
/*int rowC = 44;
* int row = 14;
* double sum = 0;
* if (ue.Count > 0)
* {
* for (int i = row; i < ue.Count + row; i++)
* {
* UOrderDetails u = (UOrderDetails)ue[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("1");
* sheet1.GetRow(i).GetCell(5).SetCellValue(u.Price.ToString("0.00"));
* sheet1.GetRow(i).GetCell(6).SetCellValue(u.Price.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("1");
* sheet1.GetRow(rowC).GetCell(5).SetCellValue(u.Price.ToString("0.00"));
* sheet1.GetRow(rowC).GetCell(6).SetCellValue(u.Price.ToString("0.00"));
*
* sum += u.Price;
* rowC++;
* }
* }*/
sheet1.GetRow(19).GetCell(6).SetCellValue(sum.ToString("0.00"));
sheet1.GetRow(20).GetCell(6).SetCellValue(Utilities.GetVAT(sum).ToString("0.00"));
sheet1.GetRow(21).GetCell(6).SetCellValue((sum + Utilities.GetVAT(sum)).ToString("0.00"));
sheet1.GetRow(49).GetCell(6).SetCellValue(sum.ToString("0.00"));
sheet1.GetRow(50).GetCell(6).SetCellValue(Utilities.GetVAT(sum).ToString("0.00"));
sheet1.GetRow(51).GetCell(6).SetCellValue((sum + Utilities.GetVAT(sum)).ToString("0.00"));
/* sheet1.GetRow(2).GetCell(1).SetCellValue(300);
* sheet1.GetRow(3).GetCell(1).SetCellValue(500050);
* sheet1.GetRow(4).GetCell(1).SetCellValue(8000);
* sheet1.GetRow(5).GetCell(1).SetCellValue(110);
* sheet1.GetRow(6).GetCell(1).SetCellValue(100);
* sheet1.GetRow(7).GetCell(1).SetCellValue(200);
* sheet1.GetRow(8).GetCell(1).SetCellValue(210);
* sheet1.GetRow(9).GetCell(1).SetCellValue(2300);
* sheet1.GetRow(10).GetCell(1).SetCellValue(240);
* sheet1.GetRow(11).GetCell(1).SetCellValue(180123);
* sheet1.GetRow(12).GetCell(1).SetCellValue(150);*/
//Force excel to recalculate all the formula while open
sheet1.ForceFormulaRecalculation = true;
WriteToFile();
}
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();
}
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();
}
