示例#1
0
        public DataTable GetProjectsOtherCostsTransationsFromDaykassa(DataTable projectsOtherCostsSheetDataTable)
        {
            for (int k = 0; k < EXP_TRANSACTIONS.Count; k++)
            {
                TRANSACTIONSRecord tr = EXP_TRANSACTIONS[k] as TRANSACTIONSRecord;

                try
                {
                    string[] recDateFormats = { "MM/dd/yyyy" };
                    DateTime recDate        = DateTime.ParseExact(tr.EXEC_DATE, recDateFormats, new CultureInfo("en-US"), DateTimeStyles.None);

                    projectsOtherCostsSheetDataTable.Rows.Add("", "",
                                                              recDate,
                                                              tr.PROJECT_SHORT_NAME,
                                                              null,
                                                              null,
                                                              null,
                                                              tr.T_DETAILS_AMOUNT,
                                                              tr.T_DETAILS_NAME);
                }
                catch (Exception)
                {
                }
            }

            return(projectsOtherCostsSheetDataTable);
        }
示例#2
0
        protected void createEXPTRANSACTIONS(LongRunningTaskBase task, OracleConnection conn, String date0, String date1)
        {
            using (OracleCommand command = conn.CreateCommand())
            {
                string sql = "SELECT TRANSACTIONS.EXEC_DATE, TRANSACTIONS.DESCR, T_DETAILS.NAME, T_DETAILS.AMOUNT, T_DETAILS.CUR, PROJECTS.SHORT_NAME, TRANSACTIONS.BITRIX_NUMBER,\n" +
                             " TRANSACTIONS.EXPENSE_TYPE_CODE, EXPENSE_TYPES.SHORT_NAME,\n" +
                             " FROMBOOK.SHORT_NAME, FROMBOOK.BOOK_ID,\n" +
                             " UNITS.UNIT_INDEX, UNITS.SHORT_NAME,\n" +
                             " T_TYPES.NAME,\n" +
                             " FROMBOOK.BOOK_INDEX, FROMBOOK.GROUP_ID,\n" +
                             " TOBOOK.SHORT_NAME, TOBOOK.BOOK_ID, TOBOOK.BOOK_INDEX, TOBOOK.GROUP_ID,\n" +
                             " TRANSACTIONS.ORG\n" +
                             " FROM DK_PROD.TRANSACTIONS\n" +
                             " LEFT OUTER JOIN DK_PROD.T_DETAILS ON TRANSACTIONS.T_ID = T_DETAILS.T_ID\n" +
                             " LEFT OUTER JOIN DK_PROD.PROJECTS ON TRANSACTIONS.PROJECT_ID = PROJECTS.PROJECT_ID\n" +
                             " LEFT OUTER JOIN DK_PROD.BOOKS FROMBOOK ON TRANSACTIONS.FROM_BOOK = FROMBOOK.BOOK_ID\n" +
                             " LEFT OUTER JOIN DK_PROD.BOOKS TOBOOK ON TRANSACTIONS.TO_BOOK = TOBOOK.BOOK_ID\n" +
                             " LEFT OUTER JOIN DK_PROD.T_TYPES ON TRANSACTIONS.T_TYPE = T_TYPES.T_TYPE\n" +
                             " LEFT OUTER JOIN DK_PROD.EXPENSE_TYPES ON TRANSACTIONS.EXPENSE_TYPE_CODE = EXPENSE_TYPES.CODE\n" +
                             " LEFT OUTER JOIN DK_PROD.UNITS ON TRANSACTIONS.UNIT_ID = UNITS.UNIT_ID\n" +
                             " WHERE TRANSACTIONS.EXEC_DATE between to_date('" + date0 + "', 'YYYY-MM-DD') AND to_date('" + date1 + "', 'YYYY-MM-DD')\n" +
                             " AND TRANSACTIONS.PROJECT_ID is not null\n" +
                             " AND TRANSACTIONS.T_TYPE IN (2, 3, 4, 5, 6, 7, 8, 12, 13, 14, 15, 16)\n" +
                             " AND TRANSACTIONS.FROM_BOOK is not null\n" +
                             " ORDER BY TRANSACTIONS.EXEC_DATE";

                command.CommandText = sql;

                using (OracleDataReader reader = command.ExecuteReader())
                {
                    try
                    {
                        int recordsCount = 0;
                        while (reader.Read())
                        {
                            TRANSACTIONSRecord tr = new TRANSACTIONSRecord();
                            EXP_TRANSACTIONS.Add(tr);
                            tr.EXEC_DATE = reader.GetOracleValue(0).ToString();
                            try
                            {
                                int i = tr.EXEC_DATE.IndexOf(' ');
                                if (i > 0)
                                {
                                    tr.EXEC_DATE = tr.EXEC_DATE.Substring(0, i);
                                }
                            }
                            catch (Exception ex)
                            {
                                tr.EXEC_DATE = "";
                            }

                            tr.DESCR          = reader.GetOracleValue(1).ToString();
                            tr.T_DETAILS_NAME = reader.GetOracleValue(2).ToString();

                            try
                            {
                                tr.T_DETAILS_AMOUNT = (double)(reader.GetDouble(3));
                            }
                            catch (Exception ex)
                            {
                                tr.T_DETAILS_AMOUNT = 0;
                            }
                            tr.T_DETAILS_CUR      = reader.GetOracleValue(4).ToString();
                            tr.PROJECT_SHORT_NAME = reader.GetOracleValue(5).ToString();

                            try
                            {
                                tr.BITRIX_NUMBER = reader.GetOracleValue(6).ToString();

                                tr.EXPENSE_TYPE_CODE       = reader.GetOracleValue(7).ToString();
                                tr.EXPENSE_TYPE_SHORT_NAME = reader.GetOracleValue(8).ToString();

                                tr.FROMBOOK_SHORT_NAME = reader.GetOracleValue(9).ToString();
                                tr.FROMBOOK_ID         = reader.GetOracleValue(10).ToString();

                                tr.UNIT_INDEX      = reader.GetOracleValue(11).ToString();
                                tr.UNIT_SHORT_NAME = reader.GetOracleValue(12).ToString();

                                tr.T_TYPE_NAME = reader.GetOracleValue(13).ToString();

                                tr.FROMBOOK_INDEX    = reader.GetOracleValue(14).ToString();
                                tr.FROMBOOK_GROUP_ID = reader.GetOracleValue(15).ToString();

                                tr.TOBOOK_SHORT_NAME = reader.GetOracleValue(16).ToString();
                                tr.TOBOOK_ID         = reader.GetOracleValue(17).ToString();
                                tr.TOBOOK_INDEX      = reader.GetOracleValue(18).ToString();
                                tr.TOBOOK_GROUP_ID   = reader.GetOracleValue(19).ToString();

                                tr.ORG = reader.GetOracleValue(20).ToString();
                            }
                            catch (Exception)
                            {
                            }

                            recordsCount++;
                            task.SetStatus(50 + 2 * recordsCount / 500, "Прочитано записей о транзакциях: " + recordsCount.ToString());
                        }
                    }
                    catch (Exception e)
                    {
                        task.SetStatus(-1, "Ошибка: " + e.Message);
                    }

                    reader.Close();
                }
            }
        }
示例#3
0
        protected static DataTable GetDKReportDataTableFromTransactions(ArrayList TRANSACTIONS, string projectShortName)
        {
            DataTable dataTable = new DataTable();

            dataTable.Columns.Add("ExecDate", typeof(DateTime)).Caption           = "Дата выполнения";
            dataTable.Columns["ExecDate"].ExtendedProperties["Width"]             = (double)14;
            dataTable.Columns.Add("BitrixNumber", typeof(string)).Caption         = "№ заявки в Битриксе";
            dataTable.Columns["BitrixNumber"].ExtendedProperties["Width"]         = (double)14;
            dataTable.Columns.Add("UnitIndex", typeof(string)).Caption            = "Код ЦФО";
            dataTable.Columns["UnitIndex"].ExtendedProperties["Width"]            = (double)9;
            dataTable.Columns.Add("UnitShortName", typeof(string)).Caption        = "ЦФО";
            dataTable.Columns["UnitShortName"].ExtendedProperties["Width"]        = (double)9;
            dataTable.Columns.Add("ExpenseTypeCode", typeof(string)).Caption      = "Код статьи";
            dataTable.Columns["ExpenseTypeCode"].ExtendedProperties["Width"]      = (double)9;
            dataTable.Columns.Add("ExpenseTypeShortName", typeof(string)).Caption = "Статья затрат";
            dataTable.Columns["ExpenseTypeShortName"].ExtendedProperties["Width"] = (double)37;
            dataTable.Columns.Add("TTypeName", typeof(string)).Caption            = "Назначение";
            dataTable.Columns["TTypeName"].ExtendedProperties["Width"]            = (double)18;
            dataTable.Columns.Add("FromBook", typeof(string)).Caption             = "Касса откуда";
            dataTable.Columns["FromBook"].ExtendedProperties["Width"]             = (double)12;
            dataTable.Columns.Add("ToBook", typeof(string)).Caption           = "Касса куда";
            dataTable.Columns["ToBook"].ExtendedProperties["Width"]           = (double)12;
            dataTable.Columns.Add("Org", typeof(string)).Caption              = "Контрагент";
            dataTable.Columns["Org"].ExtendedProperties["Width"]              = (double)12;
            dataTable.Columns.Add("ProjectShortName", typeof(string)).Caption = "Проект";
            dataTable.Columns["ProjectShortName"].ExtendedProperties["Width"] = (double)20;
            dataTable.Columns.Add("Amount", typeof(double)).Caption           = "Сумма";
            dataTable.Columns["Amount"].ExtendedProperties["Width"]           = (double)12;
            dataTable.Columns.Add("AmountCurrency", typeof(string)).Caption   = "Валюта";
            dataTable.Columns["AmountCurrency"].ExtendedProperties["Width"]   = (double)8;
            dataTable.Columns.Add("TDetailsName", typeof(string)).Caption     = "Описание";
            dataTable.Columns["TDetailsName"].ExtendedProperties["Width"]     = (double)80;
            dataTable.Columns.Add("TDescription", typeof(string)).Caption     = "Комментарий";
            dataTable.Columns["TDescription"].ExtendedProperties["Width"]     = (double)80;

            for (int k = 0; k < TRANSACTIONS.Count; k++)
            {
                TRANSACTIONSRecord tr = TRANSACTIONS[k] as TRANSACTIONSRecord;

                string[] recDateFormats = { "MM/dd/yyyy" };
                DateTime recDate        = DateTime.ParseExact(tr.EXEC_DATE, recDateFormats, new CultureInfo("en-US"), DateTimeStyles.None);

                string fromBook = "";

                if (String.IsNullOrEmpty(tr.FROMBOOK_GROUP_ID) == false &&
                    tr.FROMBOOK_GROUP_ID.ToLower().Trim().Equals("null") == false)
                {
                    fromBook += tr.FROMBOOK_GROUP_ID;
                }

                if (String.IsNullOrEmpty(tr.FROMBOOK_INDEX) == false &&
                    tr.FROMBOOK_INDEX.ToLower().Trim().Equals("null") == false)
                {
                    if (String.IsNullOrEmpty(fromBook) == false)
                    {
                        fromBook += ".";
                    }
                    fromBook += tr.FROMBOOK_INDEX;
                }

                if (String.IsNullOrEmpty(tr.FROMBOOK_SHORT_NAME) == false &&
                    tr.FROMBOOK_SHORT_NAME.ToLower().Trim().Equals("null") == false)
                {
                    fromBook += " " + tr.FROMBOOK_SHORT_NAME;
                }

                string toBook = "";

                if (String.IsNullOrEmpty(tr.TOBOOK_GROUP_ID) == false &&
                    tr.TOBOOK_GROUP_ID.ToLower().Trim().Equals("null") == false)
                {
                    toBook += tr.TOBOOK_GROUP_ID;
                }

                if (String.IsNullOrEmpty(tr.TOBOOK_INDEX) == false &&
                    tr.TOBOOK_INDEX.ToLower().Trim().Equals("null") == false)
                {
                    if (String.IsNullOrEmpty(toBook) == false)
                    {
                        toBook += ".";
                    }
                    toBook += tr.TOBOOK_INDEX;
                }

                if (String.IsNullOrEmpty(tr.TOBOOK_SHORT_NAME) == false &&
                    tr.TOBOOK_SHORT_NAME.ToLower().Trim().Equals("null") == false)
                {
                    toBook += " " + tr.TOBOOK_SHORT_NAME;
                }

                if (String.IsNullOrEmpty(projectShortName) == true ||
                    (String.IsNullOrEmpty(tr.PROJECT_SHORT_NAME) == false && projectShortName.Equals(tr.PROJECT_SHORT_NAME) == true))
                {
                    dataTable.Rows.Add(recDate.Date,
                                       GetDKStringValueForReport(tr.BITRIX_NUMBER),
                                       GetDKStringValueForReport(tr.UNIT_INDEX),
                                       GetDKStringValueForReport(tr.UNIT_SHORT_NAME),
                                       GetDKStringValueForReport(tr.EXPENSE_TYPE_CODE),
                                       GetDKStringValueForReport(tr.EXPENSE_TYPE_SHORT_NAME),
                                       GetDKStringValueForReport(tr.T_TYPE_NAME),
                                       fromBook,
                                       toBook,
                                       GetDKStringValueForReport(tr.ORG),
                                       GetDKStringValueForReport(tr.PROJECT_SHORT_NAME),
                                       tr.T_DETAILS_AMOUNT,
                                       GetDKStringValueForReport(tr.T_DETAILS_CUR),
                                       GetDKStringValueForReport(tr.T_DETAILS_NAME),
                                       GetDKStringValueForReport(tr.DESCR));
                }
            }

            return(dataTable);
        }