예제 #1
0
        private void button1_Click(object sender, EventArgs e)
        {
            provider = new NumberFormatInfo();
            provider.CurrencyDecimalSeparator = ".";

            OpenFileDialog dlg = new OpenFileDialog();

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    StreamReader sr = new StreamReader(dlg.FileName.ToString(), Encoding.GetEncoding(1251));
                    strInvoice = sr.ReadToEnd();
                    sr.Close();

                    //Удаление нулевых строк
                    strInvoice = Regex.Replace(strInvoice, @".*\s0\.0000\n", "");

                    strInvoiceHader = strInvoice.Substring(0, strInvoice.IndexOf("РЕКОМЕНДОВАНИЙ AВАНС"));
                    // richTextBox1.Text = strInvoiceHader;

                    #region Получение шапки счёта
                    string[] astr = Regex.Split(strInvoiceHader, @"\n", RegexOptions.Multiline);

                    foreach (string str in astr)
                    {
                        if (Regex.IsMatch(str, @"Номер рахунку"))
                        {
                            txtinvNumber.Text = Regex.Match(str, @"\d\d\d\d\d\d\d\d\d\d").Value.ToString();
                            txtinvDate.Text   = Regex.Match(str, @"\d\d\.\d\d\.\d\d\d\d").Value.ToString();
                        }


                        #region Формирование полей для таблицы счетов
                        if (Regex.IsMatch(str, @"ЩОМІСЯЧНА ВАРТІСТЬ ПАКЕТА ТА ПОСЛУГИ, НАДАНІ ЗА МЕЖАМИ ПАКЕТА:"))
                        {
                            txtPackageCost.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        }
                        //if (Regex.IsMatch(str, @"ПОСЛУГИ, НАДАНІ ЗА МЕЖАМИ ПАКЕТА:"))
                        //{
                        //    txtOutPackageCost.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        //}
                        //if (Regex.IsMatch(str, @"ЗАМОВЛЕНІ ДОДАТКОВІ ПОСЛУГИ:"))
                        //{
                        //    txtAdditionalServices.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        //}
                        if (Regex.IsMatch(str, @"КОНТЕНТ-ПОСЛУГИ:"))
                        {
                            txtContentServices.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        }
                        if (Regex.IsMatch(str, @"ПОСЛУГИ МІЖНАРОДНОГО РОУМІНГУ:"))
                        {
                            txtRoamingServices.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        }

                        //При внесении счёта после миграции
                        if (Regex.IsMatch(str, @"ЗНИЖКА"))
                        {
                            txtDiscount.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        }
                        if (Regex.IsMatch(str, @"ДОДАТКОВА ЗНИЖКА: "))
                        {
                            txtAddDiscount.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                            //txtDiscount.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        }
                        //

                        if (Regex.IsMatch(str, @"СПЕЦІАЛЬНІ ПОСЛУГИ МОБІЛЬНОГО ЗВ’ЯЗКУ ТА ДОСТУПУ ДО ІНТЕРНЕТ:"))
                        {
                            txtSpacialServices.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        }
                        if (Regex.IsMatch(str, @"ПОДАТОК НА ДОДАНУ ВАРТІСТЬ"))
                        {
                            txtVAT.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        }
                        if (Regex.IsMatch(str, @"ЗБІР В ПЕНСІЙНИЙ ФОНД"))
                        {
                            txtPF.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        }
                        if (Regex.IsMatch(str, @"ЗАГАЛОМ НАРАХОВАНО ЗА ПЕРІОД ЗА ВСІМА КОНТРАКТАМИ РАЗОМ З ПДВ ТА ПФ:"))
                        {
                            txtAllAmmountInBill.Text = Regex.Match(str, @"-*\d*\.\d\d").Value.ToString();
                        }
                        if (Regex.IsMatch(str, @"Особовий рахунок:"))
                        {
                            FirmAccount = str.Substring(str.IndexOf(':') + 1, str.Length - str.IndexOf(':') - 1).Trim();
                        }
                    }


                    decimal AllAmmount = 0;

                    if (txtPackageCost.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtPackageCost.Text, provider);
                    }
                    if (txtOutPackageCost.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtOutPackageCost.Text, provider);
                    }
                    if (txtAdditionalServices.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtAdditionalServices.Text, provider);
                    }
                    if (txtContentServices.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtContentServices.Text, provider);
                    }
                    if (txtRoamingServices.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtRoamingServices.Text, provider);
                    }
                    if (txtSpacialServices.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtSpacialServices.Text, provider);
                    }
                    if (txtDiscount.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtDiscount.Text, provider);
                    }

                    //Доп. скидка при миграции
                    if (txtAddDiscount.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtAddDiscount.Text, provider);
                    }


                    if (txtVAT.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtVAT.Text, provider);
                    }
                    if (txtPF.Text != "")
                    {
                        AllAmmount += Convert.ToDecimal(txtPF.Text, provider);
                    }
                    #endregion



                    // if (Math.Round(AllAmmount).ToString(provider) == txtAllAmmountInBill.Text.Substring(0, txtAllAmmountInBill.Text.Length-3))

                    if (Math.Round(AllAmmount) == Math.Round(Convert.ToDecimal(txtAllAmmountInBill.Text, provider)))
                    {
                        lblAllAmmount.Visible   = true;
                        lblAllAmmount.Text     += AllAmmount.ToString();
                        lblAllAmmount.ForeColor = Color.Green;

                        foreach (DataRow dr in ds.firm.Rows)
                        {
                            if (dr["account"].ToString() == FirmAccount)
                            {
                                MessageBox.Show(dr["firmID"] + " " + dr["name"]);
                                iFirmID = (int)dr["firmID"];
                            }
                        }

                        #region Удаление всех счетов по организации
                        DataTable             tmpInvoices = ds.Tables["invoce"];
                        IEnumerable <DataRow> query       =
                            from tmpInvoice in tmpInvoices.AsEnumerable()
                            where tmpInvoice.Field <int>("firmID") == iFirmID || tmpInvoice.Field <int>("firmID") == 0
                            select tmpInvoice;
                        List <string> iInvNumberToDelete = new List <string>();
                        foreach (DataRow p in query)
                        {
                            // MessageBox.Show(p.Field<string>("invNumber").ToString());
                            iInvNumberToDelete.Add(p.Field <string>("invNumber").ToString());
                        }
                        foreach (string str in iInvNumberToDelete)
                        {
                            DataRow dr = ds.invoce.Rows.Find(str);
                            dr.Delete();
                        }
                        #endregion


                        #region Внесение информации о новом счёте
                        DataRow drInvoice = ds.invoce.NewRow();

                        drInvoice["firmID"]      = iFirmID;
                        drInvoice["invNumber"]   = txtinvNumber.Text;
                        drInvoice["invDate"]     = txtinvDate.Text;
                        drInvoice["Discount"]    = Convert.ToDecimal(txtDiscount.Text, provider);
                        drInvoice["PackageCost"] = Convert.ToDecimal(txtPackageCost.Text, provider);
//                        drInvoice["OutPackageCost"] = Convert.ToDecimal(txtOutPackageCost.Text, provider);
                        drInvoice["RoamingServices"] = Convert.ToDecimal(txtRoamingServices.Text, provider);
//                        drInvoice["AdditionalServices"] = Convert.ToDecimal(txtAdditionalServices.Text, provider);
                        drInvoice["ContentServices"] = Convert.ToDecimal(txtContentServices.Text, provider);
                        drInvoice["SpecialServices"] = Convert.ToDecimal(txtSpacialServices.Text, provider);
                        drInvoice["VAT"]             = Convert.ToDecimal(txtVAT.Text, provider);
                        drInvoice["PF"] = Convert.ToDecimal(txtPF.Text, provider);



                        ds.invoce.Rows.Add(drInvoice);

                        //         decDiscount = Math.Abs(Convert.ToDecimal(txtDiscount.Text, provider) / (Convert.ToDecimal(txtPackageCost.Text, provider) + Convert.ToDecimal(txtOutPackageCost.Text, provider) + Convert.ToDecimal(txtAdditionalServices.Text, provider)));
                        decDiscount = Convert.ToDecimal(txtDiscount.Text, provider);


                        //    decDiscount = 0.25M;

                        //   label1.Text = decDiscount.ToString();

                        #endregion
                    }
                    else
                    {
                        lblAllAmmount.Visible   = true;
                        lblAllAmmount.Text     += "Ошибка импорта файла!";
                        lblAllAmmount.ForeColor = Color.Red;
                    }

                    #endregion


                    #region Получение данных о контрактах
                    strContacts = strInvoice.Substring(strInvoice.IndexOf("Контракт №"), strInvoice.Length - strInvoice.IndexOf("Контракт №") - 1);
//                     richTextBox1.Text = strContacts;

                    string[] aContracts = Regex.Split(strContacts, @"Контракт №");

                    string[] aContractDetail;
                    string   strActualContract    = "";
                    string   strActualServiceName = "";
                    int      iActualServiceID     = 0;



                    foreach (string str in aContracts)
                    {
                        aContractDetail = Regex.Split(str, @"\n");

                        //		Regex.Match(aContractDetail[0],@"\x20*\d{7}\x20").ToString().Trim()

                        strActualContract = Regex.Match(aContractDetail[0], @"\x20*\d{12}\x20").ToString().Trim();



                        #region Обработка информации по каждому контракту
                        if (aContractDetail.Length > 1)
                        {
                            int count = (from c in ds.contract
                                         where c.contractNumber == strActualContract
                                         select c).Count();


                            //Проверка на существование контракта в базе и его обновление
                            if (count == 0)
                            {
                                DataRow dr = ds.contract.NewRow();
                                dr["ContractNumber"] = strActualContract;
                                dr["phone"]          = Regex.Match(aContractDetail[0], @"\x20*\d{12}\x20*$").ToString().Trim();
                                dr["rateplan"]       = aContractDetail[1].Remove(0, "Тарифний Пакет:".Length).Trim();
                                dr["userID"]         = 1;
                                //dr["rateplan"] = "Test";
                                ds.contract.Rows.Add(dr);

                                ContractForm f = new ContractForm(ds, strDataFileName, strActualContract);
                                f.ShowDialog();
                            }
                            else if (count == 1)
                            {
                                DataRow dr1 = ds.contract.Rows.Find(strActualContract);
                                dr1["phone"]    = Regex.Match(aContractDetail[0], @"\x20*\d{12}\x20*$").ToString().Trim();
                                dr1["rateplan"] = aContractDetail[1].Remove(0, "Тарифний Пакет:".Length).Trim();
                            }



                            // Выполнить один раз для преобразования номеров контрактов
                            #region Преобразование контрактов к новым номерам
                            //int count = (from c in ds.contract
                            //             where c.phone == (Regex.Match(aContractDetail[0], @"\x20*\d{12}\x20*$").ToString().Trim()).Substring(3,9)
                            //             select c).Count();

                            //if (count == 0)
                            //{
                            //    DataRow dr = ds.contract.NewRow();
                            //    dr["ContractNumber"] = strActualContract;
                            //    dr["phone"] = Regex.Match(aContractDetail[0], @"\x20*\d{12}\x20").ToString().Trim();
                            //    dr["rateplan"] = aContractDetail[1].Remove(0, "Тарифний Пакет:".Length).Trim();
                            //    dr["userID"] = 1;
                            //    //dr["rateplan"] = "Test";
                            //    ds.contract.Rows.Add(dr);

                            //    ContractForm f = new ContractForm(ds, strDataFileName, strActualContract);
                            //    f.ShowDialog();

                            //}
                            //else if (count == 1)
                            //{
                            //    var cN = from c in ds.contract
                            //             where c.phone == (Regex.Match(aContractDetail[0], @"\x20*\d{12}\x20*$").ToString().Trim()).Substring(3, 9)
                            //             select c.contractNumber;

                            //    foreach(string c in cN)
                            //    {
                            //    DataRow dr1 = ds.contract.Rows.Find(c);
                            //    dr1["ContractNumber"] = strActualContract;
                            //    dr1["phone"] = Regex.Match(aContractDetail[0], @"\x20*\d{12}\x20*$").ToString().Trim();
                            //    dr1["rateplan"] = aContractDetail[1].Remove(0, "Тарифний Пакет:".Length).Trim();

                            //        int eeee = 0;
                            //    }
                            //}
                            #endregion


                            #region Обработка услуг

                            //  Обработка услуг
                            for (int i = 2; i < aContractDetail.Length; i++)
                            {
                                strActualServiceName = "";
                                iActualServiceID     = 0;

                                if (Regex.Matches(aContractDetail[i], @"\x20\.\x20\.*").Count > 0) //В названии услуги есть множество точек
                                {
                                    strActualServiceName = (Regex.Split(aContractDetail[i], @"\x20\.\x20\.*")[0]).ToString().Trim();
                                    var QueryResult =
                                        (from n in ds.Services
                                         where n.ServiceName == (Regex.Split(aContractDetail[i], @"\x20\.\x20\.*")[0]).ToString().Trim()
                                         select n).Count();

                                    // Добавление новой услуги
                                    if (QueryResult == 0)
                                    {
                                        DataRow drService = ds.Services.NewRow();
                                        drService["ServiceName"] = (Regex.Split(aContractDetail[i], @"\x20\.\x20\.*")[0]).ToString().Trim();
                                        drService["ServiceType"] = SERVICETYPE.main;
                                        drService["koeff"]       = 1.275m;
                                        ds.Services.Rows.Add(drService);


                                        ServicesForm f = new ServicesForm(ds, strDataFileName, (Int32)drService["serviceID"]);
                                        f.ShowDialog();
                                    }
                                    else if (QueryResult == 1)
                                    {
                                        /*
                                         * ServicesForm f = new ServicesForm();
                                         * f.ShowDialog();
                                         */
                                    }

                                    //Конец обработки контракта
                                    if (Regex.Split(aContractDetail[i], @"\x20\.\x20\.*")[0].ToString().Trim() == "ЗАГАЛОМ ЗА КОНТРАКТОМ (БЕЗ ПДВ ТА ПФ):")
                                    {
                                        decimal qsum =
                                            (from c in ds.bills
                                             join s in ds.Services on c.ServiceID equals s.ServiceID
                                             where c.contractNumber == strActualContract && c.invNumber == txtinvNumber.Text && s.ServiceType != "discount"
                                             select c.Ammount).Sum();

                                        decimal qdis =
                                            (from c in ds.bills
                                             join s in ds.Services on c.ServiceID equals s.ServiceID
                                             where c.contractNumber == strActualContract && c.invNumber == txtinvNumber.Text && s.ServiceType == "discount"
                                             select c.Ammount).Sum();



                                        if (qsum != (Convert.ToDecimal(Regex.Match(aContractDetail[i], @"-*\d*\.\d\d\d\d$").ToString(), provider) - qdis))
                                        {
                                            MessageBox.Show("Не совпадает сумма " + strActualContract);
                                        }

                                        break;
                                    }

                                    //continue;
                                }
                                else
                                {
                                    if (Regex.Matches(aContractDetail[i], @"-*\d*\.\d\d\d\d").Count > 0) //в названии услуги нет множества точек
                                    {
                                        strActualServiceName = (Regex.Split(aContractDetail[i], @"-*\d*\.\d\d\d\d")[0]).ToString().Trim();
                                        var QueryResult =
                                            (from n in ds.Services
                                             where n.ServiceName == (Regex.Split(aContractDetail[i], @"-*\d*\.\d\d\d\d")[0]).ToString().Trim()
                                             select n).Count();

                                        if (QueryResult == 0)
                                        {
                                            DataRow drService = ds.Services.NewRow();
                                            drService["ServiceName"] = (Regex.Split(aContractDetail[i], @"-*\d*\.\d\d\d\d")[0]).ToString().Trim();
                                            drService["ServiceType"] = SERVICETYPE.main;
                                            ds.Services.Rows.Add(drService);
                                        }
                                    }
                                }


                                DataTable tmpServices = ds.Tables["Services"];

                                IEnumerable <DataRow> query =
                                    from tmpService in tmpServices.AsEnumerable()
                                    where tmpService.Field <string>("ServiceName") == strActualServiceName
                                    select tmpService;



                                foreach (DataRow p in query)
                                {
                                    //Console.WriteLine(p.Field<string>("ServiceName"));
                                    //MessageBox.Show((p.Field<string>("ServiceName").ToString()) + "---" + (p.Field<string>("ServiceType").ToString()));

                                    if ((p.Field <string>("ServiceType").ToString() != "total"))
                                    {
                                        iActualServiceID = p.Field <Int32>("ServiceID");//Convert.ToInt32(.ToString());


                                        //          int ggguuu = 0;
                                        try
                                        {
                                            DataRow br = ds.bills.NewRow();
                                            br["invNumber"]      = txtinvNumber.Text;
                                            br["contractNumber"] = strActualContract;
                                            br["ServiceID"]      = iActualServiceID;
                                            br["Ammount"]        = Convert.ToDecimal(Regex.Match(aContractDetail[i], @"-*\d*\.\d\d\d\d$").ToString(), provider);
                                            br["AmmountT"]       = Convert.ToDecimal(Regex.Match(aContractDetail[i], @"-*\d*\.\d\d\d\d$").ToString(), provider) * p.Field <decimal>("koeff");

                                            if ((p.Field <string>("ServiceType").ToString() == "discount"))
                                            {
                                                //br["Ammount"] = 0;
                                                //br["AmmountT"] = 0;
                                                br["Discount"] = Convert.ToDecimal(Regex.Match(aContractDetail[i], @"-*\d*\.\d\d\d\d$").ToString(), provider) * p.Field <decimal>("koeff");
                                            }
                                            else
                                            {
                                                br["Discount"] = 0;
                                            }

                                            //var cn = (from c in ds.contract
                                            //          where c.contractNumber == strActualContract
                                            //          select c.rateplan).FirstOrDefault();

                                            //int kdfkjfdjkdfkdjf = 0;
                                            ////var cn = ds.contract.Where(c => c.contractNumber == txtinvNumber.Text).Select(c => c.rateplan).FirstOrDefault().ToString();
                                            //if (cn != @"GSM ' МТС ULTRA'")
                                            //{
                                            //    if (p.Field<string>("ServiceType") == SERVICETYPE.main.ToString())
                                            //    {
                                            //        br["Discount"] = ((decimal)br["AmmountT"]) * decDiscount;
                                            //    }
                                            //    else
                                            //    {
                                            //        br["Discount"] = 0;
                                            //    }
                                            //}
                                            //else
                                            //{
                                            //    br["Discount"] = 0;
                                            //}
                                            ds.bills.Rows.Add(br);
                                        }

                                        catch
                                        {
                                            MessageBox.Show("Исключение при заполнении услуг!");
                                        }
                                    }
                                }

                                #endregion

                                /*
                                 * var qr =
                                 *  from c in ds.Services
                                 *  where c.ServiceName == strActualServiceName
                                 *  select c.ServiceID
                                 *  ;
                                 * foreach (int s in qr)
                                 * {
                                 * //  MessageBox.Show(s.ToString());
                                 *
                                 * iActualServiceID = s;//Convert.ToInt32(.ToString());
                                 *
                                 * try
                                 * {
                                 *     DataRow br = ds.bills.NewRow();
                                 *     br["invNumber"] = txtinvNumber.Text;
                                 *     br["contractNumber"] = strActualContract;
                                 *     br["ServiceID"] = iActualServiceID;
                                 *     br["Ammount"] = Convert.ToDecimal(Regex.Match(aContractDetail[i], @"-*\d*\.\d\d$").ToString(), provider);
                                 *     ds.bills.Rows.Add(br);
                                 * }
                                 *
                                 * catch
                                 * {
                                 *     MessageBox.Show("Исключение при заполнении услуг!");
                                 * }
                                 *
                                 * }
                                 */


                                //          MessageBox.Show("Конец обработки услуги " + strActualServiceName + "->"
                                //              + Regex.Match(aContractDetail[i], @"-*\d*\.\d\d$").ToString() + " контракта " + strActualContract);


                                //Конец цикла обработки услуг контракта
                            }
                        }

                        #endregion

                        //MessageBox.Show(ds.bills.Rows.Count.ToString());
                    }



                    #endregion

                    MessageBox.Show("ddddd " + ds.bills.Rows.Count.ToString());
                    ds.WriteXml(strDataFileName);
                }
                catch (Exception e1)
                {
                    MessageBox.Show("LLL");
                }
            }
        }
예제 #2
0
        private void button5_Click(object sender, EventArgs e)
        {
            ContractForm f = new ContractForm(ds, strDataFileName);

            f.ShowDialog();
        }