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 ActionResult SetMetersValues(SetMetersValuesRequest request)
{
SetMetersValuesResponse response;
if (request.meters != null && request.meters.Length > 0)
{
var meters = request.meters.Select(m => new MeterDtoDa()
{
meterId = m.meterId, value = Misc.ToDouble(m.value),
}).ToArray();
response =
NativeSql.Exec("j2me_setMetersValues",
new { session = request.session, rootingId = request.rootingId, meters, })
.OneRow <SetMetersValuesResponse>();
}
else
{
response = new SetMetersValuesResponse()
{
ok = false, message = "Не верные значения счетчиков",
};
}
return(Json(response));
}
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 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 void ChangeDetails(int userId, int contractId, string contractName, int areaId, string addressStreet, string addressBuilding, string addressFlat, string phone, string changeRemarks, int archiveId, string budgetType)
{
NativeSql.Exec("contracts_changeDetails",
new
{
userId, contractId, contractName, areaId, addressStreet, addressBuilding, addressFlat, phone, changeRemarks, archiveId, budgetType
});
}
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 DirectorIndexModel GetIndexModel()
{
var tables = NativeSql.ExecMultiple("director_getIndexModel");
return(new DirectorIndexModel()
{
NextClosingPeriod = tables[0].OneRow <NextClosingPeriodDa>(),
});
}
/// <summary>
/// Start transaction
/// </summary>
/// <param name="isolationLevel">transaction level</param>
/// <returns></returns>
public virtual DbTransaction BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
{
throwTransactionIsRunningException();
_tranactionStatus = TransactionStatus.Running;
_dbConnection = NativeSql.GetConn();
_dbConnection.Open();
_dbTransaction = _dbConnection.BeginTransaction(isolationLevel);
return(_dbTransaction);
}
/// <summary>
/// Betch insert,don't return id value
/// </summary>
/// <typeparam name="T">Entity</typeparam>
/// <param name="isIdAutoGrow">Is id auto grow?</param>
/// <param name="list">Entity list</param>
public virtual void SaveOnly <T>(List <T> list, bool isIdAutoGrow = true, DbTransaction trans = null)
where T : Entity
{
var sql = new StringBuilder();
var isNeedCommit = false;
var isSupportBatch = ProviderManager.GetProvider(ProviderName).IsSupportBatch;
if (isSupportBatch)
{
sql.Append(ProviderManager.GetProvider(ProviderName).BatchBegin);
}
else if (trans == null)
{
var conn = NativeSql.GetConn();
conn.Open();
trans = conn.BeginTransaction();
isNeedCommit = true;
}
foreach (var item in list)
{
item.Key = Key;
var sqlT = item.GetSaveSql(isIdAutoGrow);
if (isSupportBatch)
{
sql.Append(sqlT);
}
else
{
NativeSql.ExecuteNonQuery(trans, sqlT);
}
}
if (isSupportBatch)
{
sql.Append(ProviderManager.GetProvider(ProviderName).BatchEnd);
if (trans == null)
{
NativeSql.ExecuteNonQuery(sql.ToString());
}
else
{
NativeSql.ExecuteNonQuery(trans, sql.ToString());
}
}
if (!isSupportBatch && isNeedCommit)
{
var conn = trans.Connection;
trans.Commit();
trans.Dispose();
conn.Close();
conn.Dispose();
}
}
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 AreasHousesModel GetAreasHouses()
{
var tables = NativeSql.ExecMultiple("areas_getHouses");
return(new AreasHousesModel()
{
Houses = tables[0].Rows <AreasHousesDa>(),
Areas = tables[1].Rows <AreasDictionaryDa>(),
});
}
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 void SaveAreasHouses(AreasHousesDa[] houses)
{
houses.ToList().ForEach((t) =>
{
t.AddressBuilding = t.AddressBuilding ?? "";
t.AddressStreet = t.AddressStreet ?? "";
});
NativeSql.Exec("areas_saveHouses", new { houses, });
}
public AreasDictionaryModel GetAreasDictionary()
{
var tables = NativeSql.ExecMultiple("areas_getDictionary");
return(new AreasDictionaryModel()
{
Areas = tables[0].Rows <AreasDictionaryDa>(),
Controllers = tables[1].Rows <CreateAreaControllerDa>(),
});
}
public ActionResult GetCloseRootingReasons(GetCloseRootingReasonsRequest request)
{
var reasons = NativeSql.Exec("j2me_getCloseRootingReasons", new { session = request.session, rootingId = request.rootingId, }).Rows <CloseReasonDto>();
var response = new GetCloseRootingResponse()
{
reasons = reasons,
};
return(Json(response));
}
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 SettingsDictionaryModel GetSetings()
{
var tables = NativeSql.ExecMultiple("settings_getDictionary");
return(new SettingsDictionaryModel()
{
Settings = tables[0].Rows <SettingsDa>(),
LastChanges = tables[1].Rows <SettingsChangeDa>(),
});
}
public ActionResult CloseRooting(CloseRootingRequest request)
{
NativeSql.Exec("j2me_closeRooting", new { session = request.session, rootingId = request.rootingId, reasonId = request.reasonId, });
CloseRootingResponse response = new CloseRootingResponse()
{
ok = true, message = ""
};
return(Json(response));
}
public ActionResult GetRootingsArrayEnclosed(GetRootingsRequest getRootingsRequest)
{
var getRootingsResponse = NativeSql.Exec("j2me_getRootings", new { session = getRootingsRequest.session, }).Rows <RootingDto>();
GetRootingsResponseArrayEmbedded embedded = new GetRootingsResponseArrayEmbedded()
{
array = getRootingsResponse,
};
return(Json(embedded));
}
public ActionResult Login(LoginRequest loginRequest)
{
var loginResponse = NativeSql.Exec(
"j2me_loginAndReturnSession",
new
{
login = loginRequest.login, password = loginRequest.password,
}).OneRow <LoginResponse>();
return(Json(loginResponse));
}
/// <summary>
/// Easy to access database,you can get any data that you want.
/// </summary>
/// <param name="sql">Any sql text</param>
/// <param name="trans">Transaction</param>
/// <returns>Any data list</returns>
public List <dynamic> List(string sql, DbTransaction trans = null)
{
if (trans == null)
{
return(NativeSql.ExecuteDynamic(sql));
}
else
{
return(NativeSql.ExecuteDynamic(trans, sql));
}
}
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 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 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 bool SetNewValue(string key, string value, int userId)
{
try
{
NativeSql.Exec("settings_setNewValue", new { key, value, userId, });
return(true);
}
catch (Exception)
{
return(false);
}
}
public CreateContractDa CreateWithSubscriber(
string type,
int userId,
string subscriberName, string subscriberPassportNumber, string subscriberPassportWhere, DateTime?subscriberPassportDate,
string subscriberAddressStreet, string subscriberAddressBuilding, string subscriberAddressFlat, string subscriberPhone,
string subscriberInn, string subscriberUgkns, string subscriberMfo, bool subscriberInvoice,
string contractName,
int contractAreaId, string contractAddressStreet, string contractAddressBuilding, string contractAddressFlat, string contractPhone,
string tariffId, int peopleRegistered,
double fixedConsumption,
CreateContractMeterInfoDa[] meters,
CPCAM_TariffOptionDa[] options,
double registrationDebt,
int archiveId, string budgetType, bool hasSewage, double allowance, string allowanceReason,
double fixedConsumptionSewage,
string subscriberUgknsName, string subscriberBankCode, string subscriberBankName, string subscriberBankAccount,
DateTime registrationDate
)
{
var res = NativeSql.Exec
(
"contracts_createWithSubscriber",
new
{
userId,
type,
subscriberName, subscriberPassportNumber, subscriberPassportWhere, subscriberPassportDate,
subscriberAddressStreet, subscriberAddressBuilding, subscriberAddressFlat, subscriberPhone,
subscriberInn, subscriberUgkns, subscriberMfo, subscriberInvoice,
contractName,
contractAreaId, contractAddressStreet, contractAddressBuilding, contractAddressFlat, contractPhone,
tariffId, peopleRegistered, fixedConsumption,
meters,
options,
registrationDebt,
archiveId, budgetType, hasSewage, allowance, allowanceReason,
fixedConsumptionSewage,
subscriberUgknsName, subscriberBankCode, subscriberBankName, subscriberBankAccount,
registrationDate
}
)
.OneRow <CreateContractDa>();
return(res);
}
public bool RegisterTransaction(int subagentId, double amount, int contractId, int userId)
{
try
{
NativeSql.Exec("subagents_registerTransaction", new { subagentId, amount, contractId, userId, });
return(true);
}
catch (Exception)
{
return(false);
}
}
