Exemplo n.º 1
0
        public void Calc(SqlConnection connection, String contract)
        {
            SqlCommand    command = new SqlCommand("SELECT Monthly_fee FROM Main WHERE Contract_num = '" + contract + "'", connection);
            SqlDataReader reader  = command.ExecuteReader();

            double     debt = 0;
            int        i    = 0;
            List <Dat> list = new List <Dat>();

            reader.Read();
            String str = reader.GetString(0);

            reader.Close();

            Dat dat;

            String[] vb = str.Split(',');

            for (i = 0; i < vb.Length; i++)
            {
                dat = new Dat();
                String[] h = vb[i].Split('_');
                dat.Fee  = Convert.ToInt32(h[0]);
                dat.Date = Convert.ToDateTime(h[1]);
                list.Add(dat);
            }
            list.Add(new Dat(list[list.Count - 1].Fee, DateTime.Today));

            int k = 0, c = 0;
            int u = 0;
            int f = 0;

            double sumFeeYear = 0, sumPayYear = 0;
            double sumFee = 0, sumPay = 0;

            bool flag = true, pFlag = true;

            bool[] sFlag = { true, true };

            DateTime date, end;

            command.CommandText = "SELECT Contract_start, Contract_end FROM ContractInf WHERE Contract_num = " + contract;
            DateTime startYear, endYear;

            reader = command.ExecuteReader();
            reader.Read();

            startYear = reader.GetDateTime(0);
            endYear   = reader.GetDateTime(1);
            reader.Close();

            //++↓↓↓++
            command = new SqlCommand("SELECT List FROM Payments WHERE Contract_num = " + contract, connection);
            reader  = command.ExecuteReader();
            reader.Read();
            str = reader.GetString(0);
            reader.Close();

            String[]   vs   = str.Split(',');
            List <Dat> dats = new List <Dat>();

            if (vs.Length > 0)
            {
                for (i = 0; i < vs.Length; i++)
                {
                    String[] s = vs[i].Split('_');
                    if (s.Length == 1)
                    {
                        break;
                    }
                    Dat d = new Dat();
                    d.Fee  = Convert.ToInt32(s[1]);
                    d.Date = Convert.ToDateTime(s[0]);
                    dats.Add(d);
                }
            }

            if (dats.Count == 0)
            {
                flag = false;
            }

            if (endYear.Year == 1900)
            {
                endYear = DateTime.Today;
            }

            string[] monthes = DateTimeFormatInfo.CurrentInfo.MonthNames;

            f = 0;
            for (i = startYear.Year; i <= endYear.Year; i++)
            {
                ListViewItem item;

                item = new ListViewItem(new string[] { "-------------------" + i.ToString() + "-------------------" });
                listViewAct.Items.Add(item);

                for (int j = 1; j <= 12; j++)
                {
                    if (startYear.Year == i && j < startYear.Month)
                    {
                        continue;
                    }

                    if ((j == DateTime.Today.Month) && (i == endYear.Year))
                    {
                        item = new ListViewItem(new string[] { "", monthes[j - 1] });
                        listViewAct.Items.Add(item);
                        for (int o = c; o < dats.Count; o++)
                        {
                            sumPayYear += dats[o].Fee;
                            item        = new ListViewItem(new string[] { "", "", "", dats[o].Fee.ToString(), dats[o].Date.ToShortDateString() });
                            listViewAct.Items.Add(item);
                        }
                        break;
                    }
                    //


                    int pay = list[k].Fee;

                    if (sFlag[0] && startYear.Day > 15)
                    {
                        f           = pay / 2;
                        sumFeeYear -= f;
                    }

                    if (pFlag && i == list[k + 1].Date.Year && j == list[k + 1].Date.Month)
                    {
                        k++;
                        pay = list[k].Fee;
                        if (k == list.Count)
                        {
                            pFlag = false;
                        }
                    }

                    if (flag && i == dats[c].Date.Year && j == dats[c].Date.Month)
                    {
                        item = new ListViewItem(new string[] { "", monthes[j - 1], pay.ToString(),
                                                               dats[c].Fee.ToString(), dats[c].Date.ToShortDateString() });
                        sumPayYear += dats[c].Fee;
                        listViewAct.Items.Add(item);
                        c++;

                        if (c == dats.Count)
                        {
                            flag = false;
                        }

                        while (flag && i == dats[c].Date.Year && j == dats[c].Date.Month)
                        {
                            item = new ListViewItem(new string[] { "", "", "", dats[c].Fee.ToString(), dats[c].Date.ToShortDateString() });
                            listViewAct.Items.Add(item);
                            sumPayYear += dats[c].Fee;
                            c++;
                            if (c == dats.Count)
                            {
                                flag = false;
                            }
                        }

                        if (sFlag[0])
                        {
                            sFlag[0] = false;
                            item     = new ListViewItem(new string[] { "", "", "", f.ToString(), "-----" });
                            listViewAct.Items.Add(item);
                        }

                        if (c == dats.Count)
                        {
                            flag = false;
                        }
                    }
                    else if (sFlag[0])
                    {
                        sFlag[0] = false;
                        item     = new ListViewItem(new string[] { "", monthes[j - 1], pay.ToString(), f.ToString(), "" });
                        listViewAct.Items.Add(item);
                    }
                    else
                    {
                        item = new ListViewItem(new string[] { "", monthes[j - 1], pay.ToString(), "", "" });
                        listViewAct.Items.Add(item);
                    }

                    sumFeeYear += pay;

                    if (j == endYear.Month && i == endYear.Year)
                    {
                        f    = u / 2;
                        item = new ListViewItem(new string[] { "", monthes[j - 1], pay.ToString(), f.ToString(), "-----" });
                        break;
                    }
                }

                item           = new ListViewItem(new string[] { "", "Обороты за период:", sumFeeYear.ToString(), sumPayYear.ToString(), "" });
                item.BackColor = Color.Gainsboro;
                listViewAct.Items.Add(item);
                sumFee    += sumFeeYear; sumPay += sumPayYear;
                sumFeeYear = 0; sumPayYear = 0;
                if (i != endYear.Year)
                {
                    item = new ListViewItem(new string[] { "Долг на 01.01." + (i + 1).ToString(), (sumFee - sumPay).ToString() });
                    listViewAct.Items.Add(item);
                }
                else
                {
                    item = new ListViewItem(new string[] { "Долг на " + DateTime.Today.ToShortDateString(),
                                                           (sumFee - sumPay).ToString() });
                    item.BackColor = Color.PeachPuff;
                    listViewAct.Items.Add(item);
                }
            }
            command.CommandText = "UPDATE Main SET Debt = " + (sumFee - sumPay).ToString() + " WHERE Contract_num = '" + contract + "'";
            command.ExecuteNonQuery();
            command.CommandText = "UPDATE ToExcel SET Debt = " + (sumFee - sumPay).ToString() + " WHERE Contract_num = '" + contract + "'";
            command.ExecuteNonQuery();
            Program.form.setButtonAct(true);
        }
Exemplo n.º 2
0
        private double DebtCalc(SqlDataReader reader, string contract)
        {
            reader.Read();
            String str = reader.GetString(0);

            reader.Close();

            List <Dat> list = new List <Dat>();

            Dat dat;

            String[] vb = str.Split(',');
            for (int i = 0; i < vb.Length; i++)
            {
                dat = new Dat();
                String[] h = vb[i].Split('_');
                dat.Fee  = Convert.ToInt32(h[0]);
                dat.Date = Convert.ToDateTime(h[1]);
                list.Add(dat);
            }

            DateTime date, end;
            int      u    = 0;
            double   debt = 0;

            for (int i = 0; i <= list.Count - 2; i++)
            {
                u    = list[i].Fee;
                date = Convert.ToDateTime(list[i].Date);

                if (date.Day > 15)
                {
                    debt += u / 2;
                    date  = date.AddMonths(1);
                }

                end = Convert.ToDateTime(list[i + 1].Date);

                while (date < end)
                {
                    debt += u;
                    date  = date.AddMonths(1);
                }
            }

            SqlCommand command = new SqlCommand("SELECT Contract_end FROM ContractInf WHERE Contract_num = '" + contract + "'", connection);

            reader = command.ExecuteReader();
            reader.Read();

            end = reader.GetDateTime(0);
            if (end.Year == 1900)
            {
                end = DateTime.Today;
            }
            reader.Close();

            date = list[list.Count - 1].Date;
            u    = list[list.Count - 1].Fee;
            while (date < end)
            {
                debt += u;
                date  = date.AddMonths(1);
            }

            debt -= u;

            command = new SqlCommand("SELECT List FROM Payments WHERE Contract_num = " + contract, connection);
            reader  = command.ExecuteReader();
            reader.Read();
            str = reader.GetString(0);
            reader.Close();

            if (str == null || str == "")
            {
                command.CommandText = "UPDATE Main SET Debt = " + debt + "WHERE Contract_num = '" + contract + "'";
                command.ExecuteNonQuery();
                return(debt);
            }

            String[] vs = str.Split(',');

            for (int i = 0; i < vs.Length; i++)
            {
                double d = Convert.ToDouble(vs[i].Split('_')[1]);
                debt -= d;
            }

            if (end != DateTime.Today && end.Day < 16)
            {
                debt -= u / 2;
            }

            command.CommandText = "UPDATE Main SET Debt = " + debt + "WHERE Contract_num = '" + contract + "'";
            command.ExecuteNonQuery();
            command.CommandText = "UPDATE ToExcel SET Debt = " + debt + "WHERE Contract_num = '" + contract + "'";
            command.ExecuteNonQuery();

            return(debt);
        }