コード例 #1
0
ファイル: Program.cs プロジェクト: eoll0882/ResidentalCalc
        static void Main(string[] args)
        {
            ServicePointManager.ServerCertificateValidationCallback = delegate { return(true); };

            HelperHouseManagementService helperHouseManagementService = new HelperHouseManagementService();
            HelperDeviceMeteringService  helperDeviceMeteringService  = new HelperDeviceMeteringService();
            HelperNsiService             helperNsiService             = new HelperNsiService();
            HelperBillService            helperBillService            = new HelperBillService();

            ResidentalCalcDataClassesDataContext db = new ResidentalCalcDataClassesDataContext();

            #region Синхронизируем справочник 51 Коммунальные услуги
            Table <RefNsiService51> refNsiService51 = db.GetTable <RefNsiService51>();

            var itemNsiReference51 = helperNsiService.GetNsiReference51(_orgPPAGUID).exportNsiItemResult;
            var nsiElement         = itemNsiReference51.Item as _NsiService.NsiItemType;
            foreach (var itemNsiElement in nsiElement.NsiElement)
            {
                if (!(refNsiService51.Any(obj => obj.Code == itemNsiElement.Code &&
                                          obj.GUID == Guid.Parse(itemNsiElement.GUID))))
                {
                    db.spInsertRefNsiService51(itemNsiElement.Code,
                                               Guid.Parse(itemNsiElement.GUID),
                                               itemNsiElement.IsActual,
                                               ((_NsiService.NsiElementStringFieldType)itemNsiElement.NsiElementField[1]).Value);
                    Console.WriteLine("NsiService51 is synchronized");
                }
                else
                {
                    foreach (var itemRef in refNsiService51.Where(s => s.GUID == Guid.Parse(itemNsiElement.GUID)))
                    {
                        if (itemRef.IsActual != itemNsiElement.IsActual)
                        {
                            itemRef.IsActual = itemNsiElement.IsActual;
                            db.SubmitChanges();
                            Console.WriteLine("Item is correct");
                        }
                        else
                        {
                            Console.WriteLine("NsiService51 already exist");
                        }
                    }
                }
            }
            #endregion

            #region Синхронизируем договоры
            Table <SupplyResourceContract> supplyResourceContracts = db.GetTable <SupplyResourceContract>();

            string typeRes = null;
            do
            {
                foreach (var tmpSupplyResourceContract in helperHouseManagementService.GetSupplyResourceContractData(_orgPPAGUID, _ExportContractRootGUID).exportSupplyResourceContractResult.Items)
                {
                    typeRes = tmpSupplyResourceContract.ToString();
                    if (tmpSupplyResourceContract.GetType() == typeof(_HouseManagementService.exportSupplyResourceContractResultType))
                    {
                        var tmpExportSupplyResourceContract = tmpSupplyResourceContract as _HouseManagementService.exportSupplyResourceContractResultType;
                        if (tmpExportSupplyResourceContract.VersionStatus == _HouseManagementService.exportSupplyResourceContractResultTypeVersionStatus.Posted &&
                            tmpExportSupplyResourceContract.Item1.GetType() == typeof(_HouseManagementService.ExportSupplyResourceContractTypeApartmentBuildingOwner))
                        {
                            if (!(supplyResourceContracts.Any(obj => obj.GIS_ContractRootGUID == Guid.Parse(tmpExportSupplyResourceContract.ContractRootGUID))))
                            {
                                db.spInsertSupplyResourceContract(((_HouseManagementService.ExportSupplyResourceContractTypeIsContract)tmpExportSupplyResourceContract.Item).ContractNumber,
                                                                  ((_HouseManagementService.ExportSupplyResourceContractTypeIsContract)tmpExportSupplyResourceContract.Item).SigningDate,
                                                                  DateTime.Parse("2079-01-01"),
                                                                  true,
                                                                  Guid.Parse(tmpExportSupplyResourceContract.ContractRootGUID));
                                Console.WriteLine("Contract is synchronized");
                            }
                            else
                            {
                                Console.WriteLine("Contract already exist");
                            }
                        }
                    }
                    else
                    {
                        _ExportContractRootGUID = tmpSupplyResourceContract.ToString();
                    }
                }
            }while (typeRes != "True");
            #endregion

            #region Синхронизация предметов договора
            Table <ContractSubject> contractSubject = db.GetTable <ContractSubject>();

            foreach (var item in supplyResourceContracts.Where(s => s.GIS_ContractRootGUID != null))
            {
                foreach (var itemContract in helperHouseManagementService.GetSupplyResourceContractDataByContractRootGUID(_orgPPAGUID, item.GIS_ContractRootGUID.ToString()).exportSupplyResourceContractResult.Items)
                {
                    if (itemContract.GetType() == typeof(_HouseManagementService.exportSupplyResourceContractResultType))
                    {
                        var tmpContract         = itemContract as _HouseManagementService.exportSupplyResourceContractResultType;
                        var itemContractSubject = tmpContract.ContractSubject as _HouseManagementService.ExportSupplyResourceContractTypeContractSubject[];
                        foreach (var tmpContractSubject in itemContractSubject)
                        {
                            if (!(contractSubject.Any(obj => obj.SupplyResourceContractGUID == item.GUID &&
                                                      obj.ServiceGUID == Guid.Parse("2270143B-A8D6-44DE-882C-0E4EC953A011") ||
                                                      obj.ServiceGUID == Guid.Parse("09C92E62-A67C-46BA-9C7C-46FC6EC72A45"))))
                            {
                                db.spInsertContractSubject(tmpContractSubject.StartSupplyDate,
                                                           tmpContractSubject.EndSupplyDate,
                                                           Guid.Parse("2270143B-A8D6-44DE-882C-0E4EC953A011"),
                                                           item.GUID);
                                Console.WriteLine("Contract subject is synchronized");
                            }
                            else
                            {
                                Console.WriteLine("Contract subject already exist");
                            }
                        }
                    }
                }
            }
            #endregion

            #region Синхронизируем адресные объекты
            Table <ObjectAddress> objectAddresses = db.GetTable <ObjectAddress>();

            foreach (var item in supplyResourceContracts.Where(s => s.GIS_ContractRootGUID != null))
            {
                foreach (var itemObjectAddress in helperHouseManagementService.GetSupplyResourceContractObjectAddressData(_orgPPAGUID, item.GIS_ContractRootGUID.ToString()).exportSupplyResourceContractObjectAddressResult.Items)
                {
                    if (itemObjectAddress.GetType() == typeof(_HouseManagementService.exportSupplyResourceContractObjectAddressResultType))
                    {
                        var tmpItemObjectAddress = itemObjectAddress as _HouseManagementService.exportSupplyResourceContractObjectAddressResultType;
                        if (!(objectAddresses.Any(obj => obj.GIS_ObjectGUID == Guid.Parse(tmpItemObjectAddress.ObjectGUID) &&
                                                  obj.SupplyResourceContractGUID == item.GUID)))
                        {
                            db.spInsertObjectAddress(Guid.Parse(item.GUID.ToString()),
                                                     Guid.Parse(tmpItemObjectAddress.FIASHouseGuid.ToString()),
                                                     tmpItemObjectAddress.ApartmentNumber,
                                                     tmpItemObjectAddress.RoomNumber,
                                                     Guid.Parse(tmpItemObjectAddress.ObjectGUID));
                            Console.WriteLine("Object address is synchronized");
                        }
                        else
                        {
                            Console.WriteLine("Object address already exist");
                        }
                    }
                }
            }
            #endregion

            #region Синхронизируем лицевые счета
            Table <Accounts> accounts = db.GetTable <Accounts>();

            foreach (var item in objectAddresses.Where(s => s.GIS_ObjectGUID != null))
            {
                foreach (var itemAccount in helperHouseManagementService.GetAccountDataByFiasGuid(_orgPPAGUID, item.FIASHouseGUID.ToString()).exportAccountResult.Items)
                {
                    if (itemAccount.GetType() == typeof(_HouseManagementService.exportAccountResultType))
                    {
                        var tmpAccount = itemAccount as _HouseManagementService.exportAccountResultType;
                        if (!(accounts.Any(obj => obj.GIS_AccountGUID == Guid.Parse(tmpAccount.AccountGUID) &&
                                           obj.ObjectAddressGUID == item.GUID)))
                        {
                            db.spInsertAccount(Guid.Parse(item.GUID.ToString()),
                                               tmpAccount.AccountNumber,
                                               true,
                                               int.Parse(tmpAccount.LivingPersonsNumber ?? "2"),
                                               Guid.Parse(tmpAccount.AccountGUID));
                            Console.WriteLine("Account is synchronized");
                        }
                        else
                        {
                            Console.WriteLine("Account already exist");
                        }
                    }
                }
            }
            #endregion

            #region Синхронизируем приборы учета
            Table <MeteringDevice> meteringDevice = db.GetTable <MeteringDevice>();

            foreach (var item in objectAddresses.Where(s => s.GIS_ObjectGUID != null))
            {
                foreach (var itemMeteringDevice in helperHouseManagementService.GetMeteringDeviceData(_orgPPAGUID, item.FIASHouseGUID.ToString()).exportMeteringDeviceDataResult.Items)
                {
                    if (itemMeteringDevice.GetType() == typeof(_HouseManagementService.exportMeteringDeviceDataResultType))
                    {
                        var tmpMeterongDevice = itemMeteringDevice as _HouseManagementService.exportMeteringDeviceDataResultType;
                        if (!(meteringDevice.Any(obj => obj.GIS_MeteringDeviceGUID == Guid.Parse(tmpMeterongDevice.MeteringDeviceRootGUID) &&
                                                 obj.ObjectAddressGUID == item.GUID)))
                        {
                            db.spInsertMeteringDevice(Guid.Parse(item.GUID.ToString()),
                                                      tmpMeterongDevice.BasicChatacteristicts.MeteringDeviceModel,
                                                      tmpMeterongDevice.BasicChatacteristicts.MeteringDeviceStamp,
                                                      tmpMeterongDevice.BasicChatacteristicts.MeteringDeviceNumber,
                                                      6,
                                                      tmpMeterongDevice.BasicChatacteristicts.InstallationDate,
                                                      tmpMeterongDevice.BasicChatacteristicts.FirstVerificationDate,
                                                      Guid.Parse(tmpMeterongDevice.MeteringDeviceRootGUID));
                            Console.WriteLine("Metering device is synchronized");
                        }
                        else
                        {
                            Console.WriteLine("Metering device already exist");
                        }
                    }
                }
            }
            #endregion

            #region Синхронизируем показания приборов учета
            Table <MeteringDeviceHistory> meteringDeviceHistory = db.GetTable <MeteringDeviceHistory>();

            foreach (var item in meteringDevice.Where(s => s.GIS_MeteringDeviceGUID != null))
            {
                foreach (var itemMeteringDeviceHistory in helperDeviceMeteringService.GetMeteringDeviceHistory(_orgPPAGUID, item.ObjectAddress.FIASHouseGUID.ToString(), item.GIS_MeteringDeviceGUID.ToString()).exportMeteringDeviceHistoryResult.Items)
                {
                    if (itemMeteringDeviceHistory.GetType() == typeof(_DeviceMeteringService.exportMeteringDeviceHistoryResultType))
                    {
                        var tmpMeteringDeviceHistory       = itemMeteringDeviceHistory as _DeviceMeteringService.exportMeteringDeviceHistoryResultType;
                        var tmpMeteringDeviceHistoryDetail = tmpMeteringDeviceHistory.Item as _DeviceMeteringService.exportMeteringDeviceHistoryResultTypeOneRateDeviceValue;
                        foreach (var itemCurrentValue in tmpMeteringDeviceHistoryDetail.Values.CurrentValue)
                        {
                            var tmpCurrentValue = itemCurrentValue as _DeviceMeteringService.OneRateCurrentMeteringValueExportType;
                            if (!(meteringDeviceHistory.Any(obj => obj.MeteringDeviceDate == tmpCurrentValue.DateValue &&
                                                            obj.MeteringDeviceValue == Convert.ToDecimal(tmpCurrentValue.MeteringValue))))
                            {
                                db.spInsertMeteringDeviceHistory(item.GUID,
                                                                 Convert.ToDecimal(tmpCurrentValue.MeteringValue),
                                                                 tmpCurrentValue.DateValue);
                                Console.WriteLine("Metering device value is synchronized");
                            }
                            else
                            {
                                Console.WriteLine("Metering device value already exist");
                            }
                        }
                    }
                }
            }
            #endregion

            #region Синхронизация плательщиков
            Table <PayerInfo> payerInfo = db.GetTable <PayerInfo>();

            foreach (var item in accounts.Where(s => s.GIS_AccountGUID != null))
            {
                foreach (var itemAccount in helperHouseManagementService.GetAccountDataByAccontGuid(_orgPPAGUID, item.GIS_AccountGUID.ToString()).exportAccountResult.Items)
                {
                    if (itemAccount.GetType() == typeof(_HouseManagementService.exportAccountResultType))
                    {
                        var tmpAccount = itemAccount as _HouseManagementService.exportAccountResultType;
                        if (!(payerInfo.Any(obj => obj.AccountGUID == item.GUID &&
                                            obj.Name == ((_HouseManagementService.AccountIndExportType)tmpAccount.PayerInfo.Item).FirstName &&
                                            obj.Surname == ((_HouseManagementService.AccountIndExportType)tmpAccount.PayerInfo.Item).Surname &&
                                            obj.GrandName == ((_HouseManagementService.AccountIndExportType)tmpAccount.PayerInfo.Item).Patronymic)))
                        {
                            var tmpAccountDetail = tmpAccount.PayerInfo as _HouseManagementService.AccountExportTypePayerInfo;
                            var tmp = tmpAccountDetail.Item as _HouseManagementService.AccountIndExportType;
                            db.spInsertPayerInfo(item.GUID,
                                                 ((_HouseManagementService.AccountIndExportType)tmpAccount.PayerInfo.Item).Surname,
                                                 ((_HouseManagementService.AccountIndExportType)tmpAccount.PayerInfo.Item).FirstName,
                                                 ((_HouseManagementService.AccountIndExportType)tmpAccount.PayerInfo.Item).Patronymic ?? null,
                                                 "11111111111",
                                                 ((_HouseManagementService.AccountIndExportTypeID)tmp.Item).Series ?? null,
                                                 ((_HouseManagementService.AccountIndExportTypeID)tmp.Item).Number ?? null,
                                                 ((_HouseManagementService.AccountIndExportTypeID)tmp.Item).IssueDate);
                            Console.WriteLine("Payer info is synchronized");
                        }
                        else
                        {
                            Console.WriteLine("Payer info already exist");
                        }
                    }
                }
            }
            #endregion

            Console.Read();

            #region Создание расчетов
            Table <Calculation> calculation = db.GetTable <Calculation>();

            foreach (var itemAccount in accounts)
            {
                try
                {
                    if (!(calculation.Any(obj => obj.AccountGUID == itemAccount.GUID &&
                                          obj.CalculationMonth == DateTime.Now.Month &&
                                          obj.CalculationYear == DateTime.Now.Year)))
                    {
                        db.spCreateCalculation(itemAccount.GUID, DateTime.Now.Month, DateTime.Now.Year);
                        Console.WriteLine("Calculation success");
                    }
                    else
                    {
                        Console.WriteLine("Calculation already exist");
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
            }
            #endregion

            #region Синхронизация квитанций
            Table <vwChargeInfo> ChargeInfo = db.GetTable <vwChargeInfo>();
            List <_BillsService.PaymentDocumentTypeChargeInfo> paymentDocumentTypeChargeInfos = new List <_BillsService.PaymentDocumentTypeChargeInfo>();
            _BillsService.PDServiceChargeTypeMunicipalServiceVolumeDeterminingMethod _determiningMethod;

            foreach (var itemCalculation in calculation.Where(s => s.GIS_PaymentDocumentNumber == null))
            {
                foreach (var itemChargeInfo in ChargeInfo.Where(s => s.CalculationGUID == itemCalculation.GUID))
                {
                    if (itemChargeInfo.VolumeDeterminingMethodChar == "N")
                    {
                        _determiningMethod = _BillsService.PDServiceChargeTypeMunicipalServiceVolumeDeterminingMethod.N;
                    }
                    else
                    {
                        if (itemChargeInfo.VolumeDeterminingMethodChar == "O")
                        {
                            _determiningMethod = _BillsService.PDServiceChargeTypeMunicipalServiceVolumeDeterminingMethod.O;
                        }
                        else
                        {
                            _determiningMethod = _BillsService.PDServiceChargeTypeMunicipalServiceVolumeDeterminingMethod.M;
                        }
                    }

                    paymentDocumentTypeChargeInfos.Add(new _BillsService.PaymentDocumentTypeChargeInfo
                    {
                        Item = new _BillsService.PDServiceChargeTypeMunicipalService
                        {
                            AccountingPeriodTotal = (decimal)itemChargeInfo.CalculationResult,                                                                //Всего начислено за расчетный период
                            TotalPayable          = (decimal)itemChargeInfo.CalculationResult,                                                                //Итого к оплате за расчетный период
                            Rate        = contractSubject.Where(s => s.GUID == itemChargeInfo.ContractSubjectGUID).First().RefServices.RefTariff.TariffValue, //Тариф
                            ServiceType = new _BillsService.nsiRef
                            {
                                Code = contractSubject.First(s => s.GUID == itemChargeInfo.ContractSubjectGUID).RefServices.RefNsiService51.Code,
                                GUID = contractSubject.First(s => s.GUID == itemChargeInfo.ContractSubjectGUID).RefServices.RefNsiService51.GUID.ToString()
                            },
                            Consumption = new _BillsService.PDServiceChargeTypeMunicipalServiceVolume[]
                            {
                                new _BillsService.PDServiceChargeTypeMunicipalServiceVolume
                                {
                                    typeSpecified = true,
                                    type          = _BillsService.PDServiceChargeTypeMunicipalServiceVolumeType.I, //(I)ndividualConsumption - индивидульное потребление (O)verallNeeds - общедомовые нужды
                                    determiningMethodSpecified = true,
                                    determiningMethod          = _determiningMethod,                               //(N)orm - Норматив (M)etering device - Прибор учета (O)ther - Иное
                                    Value = (decimal)itemChargeInfo.Volume
                                }
                            },
                            ServiceInformation = new _BillsService.ServiceInformation
                            {
                                individualConsumptionNormSpecified = true,
                                individualConsumptionNorm          = contractSubject.First().RefServices.RefStandard.StandardValue //Норматив потребления в жилых помещениях
                            }
                        }
                    });
                }

                try
                {
                    var test = helperBillService.SetPaymentDocumentData(_orgPPAGUID, itemCalculation.Accounts.GIS_AccountGUID.ToString(), paymentDocumentTypeChargeInfos).ImportResult.Items;
                    foreach (var itemTest in test)
                    {
                        if (itemTest.GetType() == typeof(_BillsService.CommonResultType))
                        {
                            var resTest = itemTest as _BillsService.CommonResultType;
                            foreach (var itemResTest in resTest.Items)
                            {
                                if (itemResTest.GetType() == typeof(_BillsService.CommonResultTypeError))
                                {
                                    var resError = itemResTest as _BillsService.CommonResultTypeError;
                                    Console.WriteLine("{0}: {1}", resError.ErrorCode, resError.Description);
                                }
                                else
                                {
                                    itemCalculation.GIS_PaymentDocumentNumber = itemResTest.ToString();
                                    db.SubmitChanges();
                                    Console.WriteLine(itemResTest);
                                }
                            }
                        }
                    }
                    Console.WriteLine("Payment document is synchronized");
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
                finally
                {
                    paymentDocumentTypeChargeInfos.Clear();
                }
            }
            #endregion

            Console.Read();
        }
コード例 #2
0
ファイル: Program.cs プロジェクト: bogich/InsertSettlements
        static void Main(string[] args)
        {
            ServicePointManager.ServerCertificateValidationCallback = delegate { return(true); };

            HelperHouseManagementService            helperHouseManagementService            = new HelperHouseManagementService();
            HelperOrganizationRegistryCommonService helperOrganizationRegistryCommonService = new HelperOrganizationRegistryCommonService();
            HelperBillService helperBillService = new HelperBillService();

            string typeRes = null;

            do
            {
                foreach (var tmpSupplyResourceContract in helperHouseManagementService.GetSupplyResourceContractData(_orgPPAGUID, _ExportContractRootGUID).exportSupplyResourceContractResult.Items)
                {
                    typeRes = tmpSupplyResourceContract.ToString();
                    if (tmpSupplyResourceContract.GetType() == typeof(_HouseManagementService.exportSupplyResourceContractResultType))
                    {
                        var tmpExportSupplyResourceContract = tmpSupplyResourceContract as _HouseManagementService.exportSupplyResourceContractResultType;
                        if (tmpExportSupplyResourceContract.VersionStatus == _HouseManagementService.exportSupplyResourceContractResultTypeVersionStatus.Posted &&
                            tmpExportSupplyResourceContract.Item1.GetType() == typeof(_HouseManagementService.ExportSupplyResourceContractTypeOrganization))
                        {
                            Console.WriteLine("ContractRootGUID: {0}", tmpExportSupplyResourceContract.ContractRootGUID);
                            _ContractRootGUID  = tmpExportSupplyResourceContract.ContractRootGUID;
                            _OrgRootEntityGUID = ((_HouseManagementService.ExportSupplyResourceContractTypeOrganization)tmpExportSupplyResourceContract.Item1).orgRootEntityGUID;
                            #region Экспорт информации о второй стороне договора
                            foreach (var itemOrgRegistry in helperOrganizationRegistryCommonService.GetOrgRegistry(_OrgRootEntityGUID).exportOrgRegistryResult.Items)
                            {
                                var ResultTypeOrgVersion = itemOrgRegistry as _OrganizationRegistryCommonService.exportOrgRegistryResultType;
                                var ItemLegalType        = ResultTypeOrgVersion.OrgVersion.Item as _OrganizationRegistryCommonService.LegalType;
                                Console.WriteLine("Organization full name: {0}", ItemLegalType.FullName);
                                _OrgRootOrganizationINN = ItemLegalType.INN;
                            }
                            #endregion
                            #region Экспорт состояния оплаты
                            foreach (var tmpExportSettlements in helperBillService.GetSettlements(_orgPPAGUID, _ContractRootGUID).exportSettlementsResult.Items)
                            {
                                if (tmpExportSettlements.GetType() == typeof(_BillsService.ErrorMessageType) &&
                                    ((_BillsService.ErrorMessageType)tmpExportSettlements).ErrorCode != "INT002012")
                                {
                                    var ErrorMessage = tmpExportSettlements as _BillsService.ErrorMessageType;
                                    BaseClasses.OutputError(ErrorMessage.ErrorCode, ErrorMessage.Description);
                                }
                                else
                                {
                                    if (tmpExportSettlements.GetType() != typeof(_BillsService.ErrorMessageType))
                                    {
                                        var resultExportSettlements          = tmpExportSettlements as _BillsService.ExportSettlementResultType;
                                        var reportingPeriodExportSettlements = resultExportSettlements.ReportingPeriod as _BillsService.ExportSettlementResultTypeReportingPeriod[];
                                        isExistValue = reportingPeriodExportSettlements
                                                       .Any(obj => obj.ReportPeriodStatus.Status == _BillsService.ExportSettlementResultTypeReportingPeriodReportPeriodStatusStatus.Posted &&
                                                            obj.Month == DateTime.Now.AddMonths(-2).Month &&
                                                            obj.Year == DateTime.Now.AddMonths(-2).Year);
                                        _SettlementGUID   = resultExportSettlements.SettlementGUID;
                                        _ContractRootGUID = null;
                                    }
                                    else
                                    {
                                        _SettlementGUID = null;
                                        isExistValue    = false;
                                    }
                                    if (isExistValue)
                                    {
                                        BaseClasses.OutputMessage(_OrgRootOrganizationINN + " Data for the period " + DateTime.Now.AddMonths(-2).Month + "." + DateTime.Now.AddMonths(-2).Year + " already exist");
                                    }
                                    else
                                    {
                                        #region Получение сведений о расчетах
                                        string connectionString = @"Data Source=storage;Initial Catalog=master;User Id=" + ConfigurationManager.AppSettings["db_login"] + ";Password="******"db_pass"];
                                        string sqlExpression    = @"WITH t1
                                                                 AS (
                                                                    SELECT 
                                                                        CASE 
                                                                            WHEN [BAccount_ID] = 62.0
                                                                                THEN d.[InvSum] + d.[InvVat]
                                                                            ELSE 0
                                                                            END AS TotalDebSum
                                                                        ,CASE 
                                                                            WHEN [BAccount_ID] = 62.5
                                                                                THEN d.[InvSum] + d.[InvVat]
                                                                            ELSE 0
                                                                            END AS TotalAvansSum
                                                                        ,CASE 
                                                                            WHEN p.PayoffType = 0
                                                                                THEN p.PayoffSum
                                                                            ELSE 0
                                                                            END AS TotalPayoffSum
                                                                    FROM [VKAbon].[dbo].[Debit] d
                                                                    LEFT JOIN [VKAbon].[dbo].[Abon_Details] ad ON d.Abon_ID = ad.Abon_ID
                                                                    LEFT JOIN (
                                                                        SELECT Debit_ID
                                                                            ,SUM(PayoffSum) AS PayoffSum
                                                                            ,PayoffType
                                                                        FROM [VKAbon].[dbo].Payoff
                                                                        GROUP BY Debit_ID
                                                                            ,PayoffType
                                                                        ) p ON d.Debit_ID = p.Debit_ID
                                                                    WHERE ad.INN = @INN
                                                                        AND YEAR(DebitDate) = @yearnumber
                                                                        AND MONTH([DebitDate]) = @monthnumber
                                                                        AND ad.DEnd = '2079-01-01'
                                                                    )
                                                                 SELECT
                                                                    SUM(TotalDebSum) AS TotalDebSum
                                                                    ,SUM(TotalAvansSum) AS TotalAvansSum
                                                                    ,SUM(TotalPayoffSum) AS TotalPayoffSum
                                                                    ,SUM(TotalDebSum) - SUM(TotalPayoffSum) - SUM(TotalAvansSum) AS TotalSaldo
                                                                 FROM t1
                                                                 HAVING
                                                                 SUM(TotalDebSum) IS NOT NULL
                                                                 AND SUM(TotalAvansSum) IS NOT NULL
                                                                 AND SUM(TotalPayoffSum) IS NOT NULL
                                                                 AND SUM(TotalDebSum) - SUM(TotalPayoffSum) - SUM(TotalAvansSum) IS NOT NULL";
                                        using (SqlConnection connection = new SqlConnection(connectionString))
                                        {
                                            connection.Open();
                                            SqlCommand   command = new SqlCommand(sqlExpression, connection);
                                            SqlParameter _INN    = new SqlParameter("@INN", _OrgRootOrganizationINN);
                                            command.Parameters.Add(_INN);
                                            SqlParameter _Month = new SqlParameter("@monthnumber", DateTime.Now.AddMonths(-2).Month);
                                            command.Parameters.Add(_Month);
                                            SqlParameter _Year = new SqlParameter("@yearnumber", DateTime.Now.AddMonths(-2).Year);
                                            command.Parameters.Add(_Year);
                                            SqlDataReader reader = command.ExecuteReader();
                                            if (reader.HasRows)
                                            {
                                                while (reader.Read())
                                                {
                                                    _TotalDebSum    = Convert.ToDecimal(reader["TotalDebSum"]);
                                                    _TotalAvansSum  = Convert.ToDecimal(reader["TotalAvansSum"]);
                                                    _TotalPayoffSum = Convert.ToDecimal(reader["TotalPayoffSum"]);
                                                    _TotalSaldo     = Convert.ToDecimal(reader["TotalSaldo"]);
                                                }
                                                #region Импорт состояния расчетов за период
                                                foreach (var itemResultImportSettlement in helperBillService.SetRSOSettlements(_orgPPAGUID, _ContractRootGUID, _SettlementGUID, DateTime.Now.AddMonths(-2), _TotalDebSum, _TotalPayoffSum, _TotalSaldo, _TotalAvansSum).ImportResult.Items)
                                                {
                                                    if (itemResultImportSettlement.GetType() == typeof(_BillsService.ErrorMessageType))
                                                    {
                                                        var ErrorMessage = itemResultImportSettlement as _BillsService.ErrorMessageType;
                                                        BaseClasses.OutputError(ErrorMessage.ErrorCode, ErrorMessage.Description);
                                                    }
                                                    else
                                                    {
                                                        var ResultType = itemResultImportSettlement as _BillsService.CommonResultType;
                                                        if (ResultType.Items[0].GetType() == typeof(_BillsService.CommonResultTypeError))
                                                        {
                                                            var ResultTypeError = ResultType.Items[0] as _BillsService.CommonResultTypeError;
                                                            BaseClasses.OutputError(ResultTypeError.ErrorCode, ResultTypeError.Description);
                                                        }
                                                        else
                                                        {
                                                            BaseClasses.OutputMessage(_OrgRootOrganizationINN + " Data import " + ResultType.Items[0]);
                                                        }
                                                    }
                                                }
                                                #endregion
                                            }
                                            else
                                            {
                                                BaseClasses.OutputMessage(_OrgRootOrganizationINN + " Settlements in our system is absent");
                                            }
                                            reader.Close();
                                            connection.Close();
                                        }
                                        #endregion
                                    }
                                }
                            }
                            #endregion
                            Console.WriteLine("-----------------------");
                        }
                    }
                    else
                    {
                        _ExportContractRootGUID = tmpSupplyResourceContract.ToString();
                    }
                }
            }while (typeRes != "True");

            Console.Read();
        }
コード例 #3
0
        static void Main(string[] args)
        {
            List <string> _Result = new List <string>();

            ServicePointManager.ServerCertificateValidationCallback = delegate { return(true); };

            #region Экспорт договоров
            HelperHouseManagementService helperHouseManagementService = new HelperHouseManagementService();
            HelperDeviceMeteringService  helperDeviceMeteringService  = new HelperDeviceMeteringService();

            string typeRes = null;
            do
            {
                foreach (var tmpSupplyResourceContract in helperHouseManagementService.GetSupplyResourceContractData(_orgPPAGUID, _ExportContractRootGUID).exportSupplyResourceContractResult.Items)
                {
                    typeRes = tmpSupplyResourceContract.ToString();
                    if (tmpSupplyResourceContract.GetType() == typeof(_HouseManagementService.exportSupplyResourceContractResultType))
                    {
                        var tmpExportSupplyResourceContract = tmpSupplyResourceContract as _HouseManagementService.exportSupplyResourceContractResultType;
                        if (tmpExportSupplyResourceContract.VersionStatus == _HouseManagementService.exportSupplyResourceContractResultTypeVersionStatus.Posted && tmpExportSupplyResourceContract.Item1.GetType() == typeof(_HouseManagementService.ExportSupplyResourceContractTypeLivingHouseOwner))
                        {
                            Console.WriteLine("ContractRootGUID: {0}", tmpExportSupplyResourceContract.ContractRootGUID);
                            _ContractRootGUID = tmpExportSupplyResourceContract.ContractRootGUID;
                            #region Экспорт ОЖФ
                            foreach (var tmp in helperHouseManagementService.GetSupplyResourceContractObjectAddressData(_orgPPAGUID, _ContractRootGUID).exportSupplyResourceContractObjectAddressResult.Items)
                            {
                                if (tmp.GetType() == typeof(bool))
                                {
                                    break;
                                }
                                var tmpObjectAddressData = tmp as _HouseManagementService.exportSupplyResourceContractObjectAddressResultType;
                                Console.WriteLine("FIASHouseGuid: {0}", tmpObjectAddressData.FIASHouseGuid);
                                _FIASHouseGuid = tmpObjectAddressData.FIASHouseGuid;
                                #region Экспорт ПУ
                                foreach (var tmpMeteringDeviceData in helperHouseManagementService.GetMeteringDeviceData(_orgPPAGUID, _FIASHouseGuid).exportMeteringDeviceDataResult.Items)
                                {
                                    if (!(tmpMeteringDeviceData is _HouseManagementService.exportMeteringDeviceDataResultType tmpExportMeteringDeviceData))
                                    {
                                        Console.ForegroundColor = ConsoleColor.DarkRed;
                                        Console.WriteLine("DeviceMetering is absent");
                                        Console.ResetColor();
                                        break;
                                    }
                                    else
                                    {
                                        Console.WriteLine("MeteringDeviceRootGUID: {0}", tmpExportMeteringDeviceData.MeteringDeviceRootGUID);
                                        _MeteringDeviceRootGUID = tmpExportMeteringDeviceData.MeteringDeviceRootGUID;
                                        #region Экспорт лицевых счетов
                                        foreach (var tmpAccountData in helperHouseManagementService.GetAccountData(_orgPPAGUID, _FIASHouseGuid).exportAccountResult.Items)
                                        {
                                            /*
                                             * Проверка прикручена из-за кривости схемы выцепления значений приборов через получение ЛС.
                                             * В ГИСе один ОЖФ может быть в договоре с УК и с частником. Т.к. ЛС ищутся по адресу,
                                             * в результате поиска такоко ОЖФ (присутствующего в обоих договорах) намвывалится два item.
                                             * Но по item'у, принадлежащему договору с УК, ЛС не найдется.
                                             */
                                            if (!(tmpAccountData is _HouseManagementService.exportAccountResultType tmpExportAccountData))
                                            {
                                                Console.ForegroundColor = ConsoleColor.DarkRed;
                                                Console.WriteLine("AccountData is absent");
                                                Console.ResetColor();
                                                break;
                                            }
                                            else
                                            {
                                                Console.WriteLine("AccountGUID: {0}", tmpExportAccountData.AccountGUID);
                                                Console.WriteLine("AccountNumber: {0}", tmpExportAccountData.AccountNumber);
                                                if (int.TryParse(tmpExportAccountData.AccountNumber, out int AccNumber))
                                                {
                                                    #region Получение значения показания прибора из "Частников"
                                                    string connectionString = @"Data Source=storage;Initial Catalog=master;User Id=" + ConfigurationManager.AppSettings["db_login"] + ";Password="******"db_pass"];
                                                    string sqlExpression    = @"SELECT CAST(t1.pvod AS INT) AS pvod
                                                               ,CAST(t2.datap AS DATE) AS datap
                                                        FROM [sql-server].[vdk].[dbo].[CHpvod] t1
                                                        INNER JOIN (SELECT plat, MAX(datap) AS datap
                                                                    FROM [sql-server].[vdk].[dbo].[CHpvod]
                                                                    GROUP BY plat) AS t2 ON t1.datap = t2.datap AND t1.plat = t2.plat
                                                        WHERE t1.vidu = 1 AND t2.datap >= @Date AND t1.plat = @Plat";
                                                    using (SqlConnection connection = new SqlConnection(connectionString))
                                                    {
                                                        connection.Open();
                                                        SqlCommand   command = new SqlCommand(sqlExpression, connection);
                                                        SqlParameter _Plat   = new SqlParameter("@Plat", tmpExportAccountData.AccountNumber);
                                                        command.Parameters.Add(_Plat);
                                                        SqlParameter _Date = new SqlParameter("@Date", DateTime.Now.AddMonths(-1));
                                                        command.Parameters.Add(_Date);
                                                        SqlDataReader reader = command.ExecuteReader();
                                                        while (reader.Read())
                                                        {
                                                            _Reading     = Convert.ToString(reader["pvod"]);
                                                            _DateReading = Convert.ToDateTime(reader["datap"]);
                                                        }
                                                        if (reader.HasRows)
                                                        {
                                                            #region Проверка на наличие показания с заданной датой
                                                            foreach (var tmpDeviceMeteringService in helperDeviceMeteringService.GetMeteringDeviceHistory(_orgPPAGUID, _FIASHouseGuid, _MeteringDeviceRootGUID).exportMeteringDeviceHistoryResult.Items)
                                                            {
                                                                if (tmpDeviceMeteringService.GetType() == typeof(_DeviceMeteringService.ErrorMessageType))
                                                                {
                                                                    Console.ForegroundColor = ConsoleColor.Red;
                                                                    Console.WriteLine("{0}: {1}", ((_DeviceMeteringService.ErrorMessageType)tmpDeviceMeteringService).ErrorCode, ((_DeviceMeteringService.ErrorMessageType)tmpDeviceMeteringService).Description);
                                                                    Console.ResetColor();
                                                                }
                                                                else
                                                                {
                                                                    var tmpExportDeviceMeteringService = tmpDeviceMeteringService as _DeviceMeteringService.exportMeteringDeviceHistoryResultType;
                                                                    var tmpExportMeteringDeviceHistoryResultTypeOneRateDeviceValue       = tmpExportDeviceMeteringService.Item as _DeviceMeteringService.exportMeteringDeviceHistoryResultTypeOneRateDeviceValue;
                                                                    var tmpExportMeteringDeviceHistoryResultTypeOneRateDeviceValueValues = tmpExportMeteringDeviceHistoryResultTypeOneRateDeviceValue.Values as _DeviceMeteringService.exportMeteringDeviceHistoryResultTypeOneRateDeviceValueValues;

                                                                    List <DateTime> _DateCurrentReading = new List <DateTime>();
                                                                    if (tmpExportMeteringDeviceHistoryResultTypeOneRateDeviceValueValues.CurrentValue != null)
                                                                    {
                                                                        foreach (var tmpCurrentValue in tmpExportMeteringDeviceHistoryResultTypeOneRateDeviceValueValues.CurrentValue)
                                                                        {
                                                                            _DateCurrentReading.Add(tmpCurrentValue.DateValue);
                                                                        }
                                                                    }

                                                                    if (_DateCurrentReading.Contains(_DateReading))
                                                                    {
                                                                        Console.ForegroundColor = ConsoleColor.DarkRed;
                                                                        Console.WriteLine("CurrentReading of current month is already exist");
                                                                        Console.ResetColor();
                                                                        _Result.Add(tmpExportAccountData.AccountNumber + ",Текущее показание " + _Reading + " с датой " + _DateReading + " уже внесено");
                                                                    }
                                                                    else
                                                                    {
                                                                        #region Импорт перечня показаний приборов учета
                                                                        foreach (var _tmpDeviceMeteringService in helperDeviceMeteringService.SetMeteringDeviceCurrentlValue(_orgPPAGUID, _FIASHouseGuid, _MeteringDeviceRootGUID, _DateReading, _Reading).ImportResult.Items)
                                                                        {
                                                                            if (_tmpDeviceMeteringService.GetType() == typeof(_DeviceMeteringService.ErrorMessageType))
                                                                            {
                                                                                Console.ForegroundColor = ConsoleColor.Red;
                                                                                Console.WriteLine("{0}: {1}", ((_DeviceMeteringService.ErrorMessageType)_tmpDeviceMeteringService).ErrorCode, ((_DeviceMeteringService.ErrorMessageType)_tmpDeviceMeteringService).Description);
                                                                                Console.ResetColor();
                                                                                _Result.Add(tmpExportAccountData.AccountNumber + "," + ((_DeviceMeteringService.ErrorMessageType)_tmpDeviceMeteringService).ErrorCode + "" + ((_DeviceMeteringService.ErrorMessageType)_tmpDeviceMeteringService).Description);
                                                                            }
                                                                            else
                                                                            {
                                                                                var tmpImportDeviceMeteringService = _tmpDeviceMeteringService as _DeviceMeteringService.CommonResultType;
                                                                                foreach (var tmpCommonResultType in tmpImportDeviceMeteringService.Items)
                                                                                {
                                                                                    if (tmpCommonResultType.GetType() == typeof(_DeviceMeteringService.CommonResultTypeError))
                                                                                    {
                                                                                        Console.ForegroundColor = ConsoleColor.Red;
                                                                                        Console.WriteLine("{0}: {1}", ((_DeviceMeteringService.CommonResultTypeError)tmpCommonResultType).ErrorCode, ((_DeviceMeteringService.CommonResultTypeError)tmpCommonResultType).Description);
                                                                                        Console.ResetColor();
                                                                                        _Result.Add(tmpExportAccountData.AccountNumber + "," + ((_DeviceMeteringService.CommonResultTypeError)tmpCommonResultType).ErrorCode + " " + ((_DeviceMeteringService.CommonResultTypeError)tmpCommonResultType).Description);

                                                                                        if (((_DeviceMeteringService.CommonResultTypeError)tmpCommonResultType).ErrorCode == "SRV007016" || ((_DeviceMeteringService.CommonResultTypeError)tmpCommonResultType).ErrorCode == "SRV007103")
                                                                                        {
                                                                                            #region Отправка уведомления
                                                                                            string _content = "Ввиду того, что у прибора учета, относящегося к лицевому счету " + tmpExportAccountData.AccountNumber + " закончился межповерочный интервал, предлагаем в срок до " + DateTime.Today.AddDays(60).ToShortDateString() + " выполнить работы по установке поверенного прибора учета с сохранением места его установки, схемы водомерного узла и калибра прибора учета. Записаться на приемку прибора учета можно по тел. 371-57-03, 371-44-43 или на сайте МУП Водоканал https://www.водоканалекб.рф/услуги/электронный-документооборот";
                                                                                            foreach (var itemImportNotificationData in helperHouseManagementService.SetNotificationData(_orgPPAGUID, _FIASHouseGuid, _content).ImportResult.Items)
                                                                                            {
                                                                                                var tmpImportNotificationData = itemImportNotificationData as _HouseManagementService.CommonResultType;
                                                                                                foreach (var tmpCommonResult in tmpImportNotificationData.Items)
                                                                                                {
                                                                                                    if (tmpCommonResult.GetType() == typeof(_HouseManagementService.CommonResultTypeError))
                                                                                                    {
                                                                                                        Console.ForegroundColor = ConsoleColor.Red;
                                                                                                        Console.WriteLine("{0}: {1}", ((_HouseManagementService.CommonResultTypeError)tmpCommonResult).ErrorCode, ((_HouseManagementService.CommonResultTypeError)tmpCommonResult).Description);
                                                                                                        Console.ResetColor();
                                                                                                        _Result.Add(tmpExportAccountData.AccountNumber + "," + ((_HouseManagementService.CommonResultTypeError)tmpCommonResult).ErrorCode + " " + ((_HouseManagementService.CommonResultTypeError)tmpCommonResult).Description);
                                                                                                    }
                                                                                                    else
                                                                                                    {
                                                                                                        Console.WriteLine("Send message about checking of device metering {0}", tmpCommonResult);
                                                                                                        _Result.Add(tmpExportAccountData.AccountNumber + ",Отпралено уведомление о необходимости поверки прибора учета " + tmpCommonResult);
                                                                                                    }
                                                                                                }
                                                                                            }
                                                                                            #endregion
                                                                                        }
                                                                                    }
                                                                                    else
                                                                                    {
                                                                                        Console.ForegroundColor = ConsoleColor.Green;
                                                                                        Console.WriteLine("CurrentValue {0} insert with date {1}", _Reading, tmpCommonResultType);
                                                                                        Console.ResetColor();
                                                                                        _Result.Add(tmpExportAccountData.AccountNumber + ",Текущее показание " + _Reading + " внесено " + tmpCommonResultType);
                                                                                    }
                                                                                }
                                                                            }
                                                                        }
                                                                        #endregion
                                                                    }
                                                                }
                                                            }
                                                            #endregion
                                                        }
                                                        else
                                                        {
                                                            Console.ForegroundColor = ConsoleColor.DarkRed;
                                                            Console.WriteLine("CurrentDevice in our system is absent");
                                                            Console.ResetColor();
                                                            _Result.Add(tmpExportAccountData.AccountNumber + ",Показания не найдены");
                                                        }
                                                        reader.Close();
                                                        connection.Close();
                                                    }
                                                    #endregion
                                                }
                                                else
                                                {
                                                    Console.ForegroundColor = ConsoleColor.DarkRed;
                                                    Console.WriteLine("AccountData is invalid format");
                                                    Console.ResetColor();
                                                    _Result.Add(tmpExportAccountData.AccountNumber + ",Неверный формат ЛС");
                                                }
                                            }
                                        }
                                        #endregion
                                    }
                                }