示例#1
0
        private void LoadTypeDevice()
        {
            CustomRetrieverDO crdo  = new CustomRetrieverDO();
            List <Book>       lbook = crdo.RetrieveWPTypeDevice();

            ddlTypeDevice.DataSource     = lbook;
            ddlTypeDevice.DataTextField  = "Title";
            ddlTypeDevice.DataValueField = "ID";
            ddlTypeDevice.DataBind();
        }
示例#2
0
        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()));
            }
        }
示例#3
0
        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()));
            }
        }
示例#4
0
        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()));
             * }*/
        }
示例#5
0
        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()));
            }
        }
示例#6
0
        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);
        }
示例#7
0
        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);
        }
示例#8
0
        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();
        }
示例#9
0
        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();
        }
示例#10
0
        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();
        }
示例#11
0
        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();
        }