public ContractsSearchResult Search(string key, string street, string building, string appartment, string areaName, string contractName, string controllerName, string bill)
{
key = key.Trim();
street = street.Trim();
building = building.Trim();
appartment = appartment.Trim();
areaName = areaName.Trim();
contractName = contractName.Trim();
controllerName = controllerName.Trim();
bill = bill.Trim();
var tables = NativeSql.ExecMultiple("contracts_search", new { key, street, building, appartment, areaName, contractName, controllerName, receiptNo = bill });
var result = new ContractsSearchResult
{
Key = key,
Street = street,
Streets = new StreetsProvider().GetAllStreets(),
Building = building,
Appartment = appartment,
Area = areaName,
ContractName = contractName,
ControllerName = controllerName,
Bill = bill,
Contracts = tables.Length > 0 ? tables[0].Rows <ContractSearchResultDa>() : new ContractSearchResultDa[] {},
Subscribers = tables.Length > 1 ? tables[1].Rows <SubscriberSearchResultDa>() : new SubscriberSearchResultDa[] {},
};
return(result);
}
public ShowContractModel GetContract(int contractId)
{
var tables = NativeSql.ExecMultiple("contracts_getDetails", new { contractId, });
var subscriber = tables[0].OneRow <SubscriberDetailsDa>();
return(subscriber != null
? new ShowContractModel()
{
Subscriber = subscriber,
Details = tables[1].OneRow <ContractDetailsDa>(),
Options = tables[2].Rows <ContractOptionsDa>(),
Meters = tables[3].Rows <OshBusinessModel.Da.ShowContract.ContractMeterDa>(),
Areas = tables[4].Rows <AreasDictionaryDa>(),
ChangeDetails = tables[5].Rows <ContractChangeDetailsDa>(),
ChangeParameters = tables[6].Rows <ContractParametersDa>(),
PossibleOptions = tables[7].Rows <AddContractOptionDa>(),
MetersActions = tables[8].Rows <ContractChangeMeterActionDa>(),
TariffOptionsActions = tables[9].Rows <ContractChangeTariffOptionActionDa>(),
TariffValues = tables[10].Rows <ContractChangeTariffOptionValueDa>(),
ChangeActions = tables[11].Rows <ContractChangeActionDa>(),
Bill = new BillsProvider().ComposeBill(tables, 12),
Subagents = tables[17].Rows <ContractSubagentDa>(),
Transactions = tables[18].Rows <ContractTransactionDa>(),
Bills = tables[19].Rows <ContractBillAmountDa>(),
MetersDetails = tables[20].Rows <ContractChangeMeterDetailsDa>(),
MakePaymentUsers = tables[21].Rows <MakePaymentUserDa>(),
MeterValues = tables[22].Rows <ContractMeterValueDa>(),
Tariffs = tables[23].Rows <ShowContractTariffDa>(),
}
: null);
}
public ContractStatementModel GetStatement(int contractId, int startPeriodId, int endPeriodId)
{
var tables = NativeSql.ExecMultiple("contracts_statement", new { contractId, startPeriodId, endPeriodId, });
var model = tables[0].OneRow <ContractStatementModel>();
if (model != null)
{
model.Found = true;
}
else
{
model = new ContractStatementModel()
{
Found = false,
ContractId = contractId,
StartPeriodId = startPeriodId,
EndPeriodId = endPeriodId
};
}
model.BillingPeriods = tables[1].Rows <BillingPeriodDa>();
model.Statements = tables[2].Rows <ContractStatementDa>();
return(model);
}
public GetInvoicesForPrintingModel GetInvoicesForPrinting(int periodId)
{
var tables = NativeSql.ExecMultiple("billing_printInvoices", new { periodId });
if (tables.Length <= 1)
{
return(null);
}
GetInvoicesForPrintingModel model = new GetInvoicesForPrintingModel()
{
PeriodId = periodId
};
model.BillingPeriods = tables[1].Rows <OshBusinessModel.Da.SubagentsBillingPeriods.BillingPeriodDa>();
model.Invoices = tables[2].Rows <InvoiceDa>();
var invoiceContracts = tables[3].Rows <InvoiceContractDa>();
foreach (var invoice in model.Invoices)
{
invoice.Contracts = invoiceContracts.Where(ic => ic.SubscriberId == invoice.SubscriberId).ToArray();
}
return(model);
}
public SubagentTransactionsModel GetTransactions(int subagentId)
{
var tables = NativeSql.ExecMultiple("subagents_getTransactions", new { subagentId, });
var model = tables[0].OneRow <SubagentTransactionsModel>();
model.Transactions = tables[1].Rows <SubagentTransactionDa>();
return(model);
}
public InvoiceDa GetInvoiceForSubscriber(int subscriberId)
{
var tables = NativeSql.ExecMultiple("billing_printLastInvoice", new { subscriberId });
var invoice = tables[0].OneRow <InvoiceDa>();
invoice.Contracts = tables[1].Rows <InvoiceContractDa>();
return(invoice);
}
public HasAllowanceReport GetHasAllowanceReport()
{
var tables = NativeSql.ExecMultiple("allowance_hasAllowanceReport");
return(new HasAllowanceReport {
Controllers = tables[0].Rows <AllowanceReportControllerDa>(), Tariffs = tables[1].Rows <AllowanceReportTariffDa>(), Items = tables[2].Rows <AllowanceReportItemDa>()
});
}
public DirectorIndexModel GetIndexModel()
{
var tables = NativeSql.ExecMultiple("director_getIndexModel");
return(new DirectorIndexModel()
{
NextClosingPeriod = tables[0].OneRow <NextClosingPeriodDa>(),
});
}
public SettingsDetailsModel GetSettingsDetails(string key)
{
var tables = NativeSql.ExecMultiple("settings_getDetails", new { key, });
return(new SettingsDetailsModel()
{
Details = tables[0].OneRow <SettingsDa>(),
Changes = tables[1].Rows <SettingsChangeDa>(),
});
}
public MobileContractModel ShowContractsMobile(int contractId)
{
var tables = NativeSql.ExecMultiple("mobiles_getContract", new { contractId, });
var model = tables[0].OneRow <MobileContractModel>();
model.Options = tables[1].Rows <OshBusinessModel.Da.ShowContract.ContractOptionsDa>();
model.Meters = tables[2].Rows <OshBusinessModel.Da.ShowContract.ContractMeterDa>();
return(model);
}
public SettingsDictionaryModel GetSetings()
{
var tables = NativeSql.ExecMultiple("settings_getDictionary");
return(new SettingsDictionaryModel()
{
Settings = tables[0].Rows <SettingsDa>(),
LastChanges = tables[1].Rows <SettingsChangeDa>(),
});
}
public BuilkPaymentsModel GetStartInfoForBulkPayments()
{
var tables = NativeSql.ExecMultiple("customs_getInfoForBulkPayments");
return(new BuilkPaymentsModel()
{
SubagentsUsers = tables[0].Rows <ShortSubagentOrUserInfo>(),
Streets = tables[1].Rows <StreetWrapper>().Select(sw => sw.Street).ToArray()
});
}
public AreasHousesModel GetAreasHouses()
{
var tables = NativeSql.ExecMultiple("areas_getHouses");
return(new AreasHousesModel()
{
Houses = tables[0].Rows <AreasHousesDa>(),
Areas = tables[1].Rows <AreasDictionaryDa>(),
});
}
public AreasDictionaryModel GetAreasDictionary()
{
var tables = NativeSql.ExecMultiple("areas_getDictionary");
return(new AreasDictionaryModel()
{
Areas = tables[0].Rows <AreasDictionaryDa>(),
Controllers = tables[1].Rows <CreateAreaControllerDa>(),
});
}
public SubscriberDictionaryDa GetSubscribersDictionary(string type, int itemsPerPage, int pageNumber, string firstLetter)
{
var tables = NativeSql.ExecMultiple("subscribers_getDictionary", new { type, itemsPerPage, pageNumber, firstLetter, });
var da = tables[0].OneRow <SubscriberDictionaryDa>();
da.Subscribers = tables[1].Rows <SubscribersDictionaryItemDa>();
da.Streets = new StreetsProvider().GetAllStreets();
return(da);
}
public TariffDictionaryDa[] GetTariffsArchiveDictionary()
{
var tables = NativeSql.ExecMultiple("tariffs_getDictionary", new { enabled = false, });
var tariffs = tables[0].Rows <TariffDictionaryDa>();
var tariffOptions = tables[1].Rows <TariffOptionDictionaryDa>();
tariffs.ToList().ForEach(
t => t.TariffOptions = tariffOptions.Where(to => to.TariffSubtype == t.Subtype).ToArray());
return(tariffs);
}
public BulkPaymentContractInfoDa GetContractInfoByArchiveId(string keyValue, string searchCriteria)
{
var tables = NativeSql.ExecMultiple("customs_bulkPaymentGetContractInfo", new { keyValue, searchCriteria });
var result = tables[0].OneRow <BulkPaymentContractInfoDa>();
if (tables.Length > 1)
{
result.Options = tables[1].Rows <BkciOptionsDa>();
}
return(result);
}
public RootingsBuildingsModel GetRootingsBuildings(int areaId, string street)
{
var tables = NativeSql.ExecMultiple("rootings_getBuildings", new { areaId, street, });
var model = tables[0].OneRow <RootingsBuildingsModel>();
if (model != null)
{
model.Buildings = tables[1].Rows <RootingsBuildingsDa>();
}
return(model);
}
public VisaRequiredModel GetVisaRequiredItems()
{
var tables = NativeSql.ExecMultiple("director_getVisaRequiredItems");
var model = new VisaRequiredModel()
{
Contracts = tables[0].Rows <ContractDa>(),
Subscribers = tables[1].Rows <SubscriberDa>(),
Settings = tables[2].Rows <SettingsDa>(),
};
return(model);
}
public WithoutAllowanceReport GetWithoutAllowanceReport()
{
var tables = NativeSql.ExecMultiple("allowance_withoutAllowanceReport");
return(new WithoutAllowanceReport
{
Controllers = tables[0].Rows <AllowanceReportControllerDa>(),
Tariffs = tables[1].Rows <AllowanceReportTariffDa>(),
TariffOptions = tables[2].Rows <AllowanceReportTariffOptionDa>(),
Items = tables[3].Rows <AllowanceReportItemDa>(),
ItemOptions = tables[4].Rows <AllowanceReportItemOptionDa>(),
});
}
public RootingsContractsModel GetRootingsContracts(int areaId, string street, string building)
{
var tables = NativeSql.ExecMultiple("rootings_getContracts", new { areaId, street, building, });
var model = tables[0].OneRow <RootingsContractsModel>();
if (model != null)
{
model.Contracts = tables[1].Rows <RootingsContractsDa>();
model.Areas = tables[2].Rows <RootingsAreasDa>();
}
return(model);
}
public void ChangeTariffPrice(int userId, string tariffId, string tariffName, double litersPerPersonPerDay, double waterPricePerCubicMeter, double sewagePricePerCubicMeter)
{
NativeSql.ExecMultiple("tariffs_changePrice",
new
{
tariffId,
tariffName,
litersPerPersonPerDay,
waterPricePerCubicMeter,
sewagePricePerCubicMeter,
userId,
});
}
public SubagentsBillingPeriodsModel GetBillingPeriodsModel(int periodId)
{
var tables = NativeSql.ExecMultiple("accountant_getSubagentsStatisticsByBillingPeriods", new { periodId, });
var model = tables[0].OneRow <SubagentsBillingPeriodsModel>();
if (model != null)
{
model.Periods = tables[1].Rows <BillingPeriodDa>();
model.Subagents = tables[2].Rows <SubagentDa>();
}
return(model);
}
public ReconciliationByControllersReport GetReconciliationByControllersReport(DateTime dateStart, DateTime dateEnd)
{
var tables = NativeSql.ExecMultiple("accountant_getReconciliationReport_byControllers", new { dateStart, dateEnd });
return
(new ReconciliationByControllersReport()
{
DateStart = dateStart,
DateEnd = dateEnd,
Users = tables[0].Rows <RbcUserDa>(),
Subagents = tables[1].Rows <RbcSubagentDa>(),
Transactions = tables[2].Rows <RbcTransactionDa>(),
});
}
public CreateContractModel GetDataForCreateContract(int subscriberId)
{
var tables = NativeSql.ExecMultiple("contracts_getDataForCreating", new { subscriberId, });
var model =
new CreateContractModel()
{
Areas = tables[0].Rows <AreasDictionaryDa>(),
Subscriber = tables[1].OneRow <CreateContractSubscriberDa>(),
Tariffs = tables[2].Rows <CreateContractTariffDa>(),
Options = tables[3].Rows <CreateContractTariffOptionDa>(),
Streets = tables[4].Rows <StreetWrapper>().Select(sw => sw.Street).ToArray(),
};
return(model);
}
public TariffOptionDetailsModel GetTariffOptionDetails(string semanticId)
{
var tables = NativeSql.ExecMultiple("tariffsOptions_getDetails", new { semanticId, });
var details = tables[0].OneRow <TariffOptionDetalisDa>();
return
(details == null
? null
: new TariffOptionDetailsModel()
{
Details = details,
Changes = tables[1].Rows <TariffOptionChangesDa>(),
Tariffs = tables[2].Rows <TariffOptionTariffsDa>(),
});
}
public TariffDetailsModel GetDetails(string semanticId)
{
var tables = NativeSql.ExecMultiple("tariffs_getDetails", new { semanticId, });
var curState = tables[0].OneRow <TariffDictionaryDa>();
return
(curState == null
? null
: new TariffDetailsModel()
{
CurrentState = curState,
Changes = tables[1].Rows <TariffChangesDa>(),
TariffOptions = tables[2].Rows <TariffOptionDictionaryDa>(),
});
}
public UserDetailsModel GetUserDetails(int userId)
{
var tables = NativeSql.ExecMultiple("users_getDetails", new { userId, });
var details = tables[0].OneRow <UserDetailsDa>();
return(details == null
? null
: new UserDetailsModel()
{
Details = details,
Actions = tables[1].Rows <UserActionDa>(),
DetailsChanges = tables[2].Rows <UserDetailsChangeDa>(),
Roles = tables[3].Rows <UserRoleChangeDa>(),
PasswordChanges = tables[4].Rows <UserChangePasswordDa>(),
});
}
public FundRaisingPlanModel GetFundRaisingPlan(int periodId)
{
var tables = NativeSql.ExecMultiple("accountant_fundRaisingPlan", new { periodId });
var model = tables[0].OneRow <FundRaisingPlanModel>();
if (model == null)
{
return(null);
}
model.BillingPeriods = tables[1].Rows <BillingPeriodDa>();
model.Controllers = tables[2].Rows <FundRaisingPlanDa>();
return(model);
}
public ReportByControllersModel GetReportByAreas(int startPeriodId, int endPeriodId)
{
var tables = NativeSql.ExecMultiple("accountant_reportByAreas", new { startPeriodId, endPeriodId, });
var model = tables[0].OneRow <ReportByControllersModel>();
model.BillingPeriods = tables[1].Rows <BillingPeriodDa>();
if (model.BillingPeriods == null || model.BillingPeriods.Length <= 0)
{
return(null);
}
model.Report = tables[2].Rows <ReportByControllersDa>();
return(model);
}