Beispiel #1
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();
        }
Beispiel #2
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();
        }