public static tbl_Cashflow GetParentCashflow(string parentCashflowNumber, string date, Entities db)
{
DateTime dt = DateTime.Parse(date);
if (db.tbl_Cashflow.Count(x => x.Obj1cDocNumIn == parentCashflowNumber && x.DocDate == dt) > 1)
return null;
return db.tbl_Cashflow.FirstOrDefault(x => x.Obj1cDocNumIn == parentCashflowNumber && x.DocDate == dt);
}
public static Guid? GetPeriodID(Entities db, DateTime date)
{
tbl_Period p = db.tbl_Period.FirstOrDefault(x => x.StartDate <= date && date <= x.DueDate);
if (p == null)
return null;
else
return p.ID;
}
void UploadToDatabase(string connectionString, List<BankStatement> statements, DateTime startDate, DateTime endDate)
{
Console.WriteLine("UploadToDatabase");
string companyID = Helper.getDs_CompanyID(company);
using (Entities db = new Entities(connectionString)) // поднимаем подключение к БД
{
DateTime compareDate = startDate;
do
{
UpdateCashflowForDate(statements, compareDate, companyID, db); // для каждой даты осуществляем сравнение выгруженнных из 1С и существующих в ТС платежей
compareDate = compareDate.AddDays(1);
} while (compareDate <= endDate); // проходим по всем датам из указанного в конфигурационном файле диапазона
}
}
public static Guid? GetCashflowClauseID(Entities db, CashflowClause clause1c, Company company)
{
string clauseCode = "1С" + String.Format("{0,5}", clause1c.Код).Replace(" ", "0");
if (clauses.Count == 0)
{
clauses = (from x in db.tbl_CashflowClause select x).ToList();
}
if (clauses.Count(x => x.Code == clauseCode) == 0)
{
string clauseName = clause1c.Наименование.Trim();
if(clauseName.Length> 200)
{
clauseName = clauseName.Substring(0, 200);
}
string clauseDescr = (clause1c.ВидДвижения + ": " + clause1c.РазрезДеятельности).Trim();
if (clauseDescr.Length > 200)
{
clauseDescr = clauseDescr.Substring(0, 200);
}
clauseCode = clauseCode.Trim();
if (clauseCode.Length > 200)
{
clauseCode = clauseCode.Substring(0, 200);
}
//add clause
tbl_CashflowClause clauseTs = new tbl_CashflowClause
{
ID = Guid.NewGuid(),
Name = clauseName,
Code = clauseCode,
CreatedByID = new Guid(Constants.DefaultAdminID),
CreatedOn = DateTime.Now,
Description = clauseDescr,
ExpenseDevideType = null,
ExpenseTypeID = null,
GroupID = null,
IsTZP = null,
ModifiedByID = new Guid(Constants.DefaultAdminID),
ModifiedOn = DateTime.Now,
TypeID = null
};
db.tbl_CashflowClause.Add(clauseTs);
db.SaveChanges();
clauses.Add(clauseTs);
return clauseTs.ID;
}
return clauses.FirstOrDefault(x => x.Code == clauseCode).ID;
}
public static void GrantToManagerAccessToCashflow(tbl_Cashflow cashflow, Entities db)
{
if (cashflow.ManagerID.HasValue)
{
if (db.tbl_CashflowRight.Count(x => x.RecordID == cashflow.ID && x.AdminUnitID == cashflow.ManagerID) == 0)
{
Guid managerAdminUnitID = db.tbl_AdminUnit.FirstOrDefault(x => x.UserContactID == cashflow.ManagerID).ID;
tbl_CashflowRight rights = new tbl_CashflowRight
{
AdminUnitID = managerAdminUnitID,
CanChangeAccess = 1,
CanDelete = 0,
CanRead = 1,
CanWrite = 1,
ID = Guid.NewGuid(),
RecordID = cashflow.ID
};
db.tbl_CashflowRight.Add(rights);
}
}
}
public void ImportManualCashflows(string sourceXmlFile, string destDbConnectionString, DateTime startDate, DateTime endDate)
{
List<ManualCashflow> cashflows = ParseXmlFileMc(sourceXmlFile);
using (StatementsImporterLib.ADO.Entities db = new Entities(destDbConnectionString)) // поднимаем подключение к БД
{
foreach (ManualCashflow mc in cashflows)
{
string parentCashflowNumber = mc.Number.Replace("пп", "").Replace(".", "");
tbl_Cashflow parentCashflow = DbHelper.GetParentCashflow(parentCashflowNumber, mc.Date, db);
if (parentCashflow == null)
{
Console.WriteLine("{0} не найден", parentCashflowNumber);
}
else
{
tbl_Cashflow childCashflow = mc.toTsCashflow(parentCashflow, db);
tbl_CashflowInCashflow cic = new tbl_CashflowInCashflow
{
CreatedByID = new Guid(Constants.DefaultAdminID),
CreatedOn = DateTime.Now,
ID = Guid.NewGuid(),
ModifiedByID = new Guid(Constants.DefaultAdminID),
ModifiedOn = DateTime.Now,
ChildID = childCashflow.ID,
ParentID = parentCashflow.ID
};
Console.WriteLine("{0} ({1}): => {2}", parentCashflow.CFNumber, parentCashflow.Obj1cDocNumIn, childCashflow.Amount);
int childCount = db.tbl_CashflowInCashflow.Count(x => x.ParentID == parentCashflow.ID);
childCashflow.CFNumber += "xx" + (char)('a' + childCount);
db.tbl_Cashflow.Add(childCashflow);
DbHelper.GrantToManagerAccessToCashflow(childCashflow, db); // раздаём права на новый платёж его менеджеру
db.tbl_CashflowInCashflow.Add(cic);
db.SaveChanges();
}
}
}
}
private Guid? GetContractID(Entities db, Transfer t)
{
Guid? ContractID = null;
Subconto Contract = GetSubconto(t, SubcontoType.Договор);
if (Contract != null)
{
string ContractNumber = ParseContractNumber(Contract.Наименование);
ContractID = GetContractID(db, ContractNumber);
}
return ContractID;
}
private void CommitTransferToLocalList(Entities db, Transfer t, BankStatement statement)
{
tbl_Cashflow c = CreateCashflow(db, t, statement.ДатаДок);
statement.Cashflows.Add(c);
}
void UpdateCashflowForDate(DateTime compareDate, string companyID, Entities db)
{
Console.WriteLine(compareDate.ToShortDateString());
List<Transfer> list1C = getTransfersFrom1c(compareDate); // выбираем платежи из выписки 1С на дату
List<tbl_Cashflow> listTs = getTransfersFromTs(compareDate, companyID, db); // выбираем платежи из ТС на дату
Update1cDocNumIns(db, list1C, listTs, compareDate); // обновить номераДокВходящих для уже импортированных записей // НЕ ПОМНЮ, ЗАЧЕМ ЭТО
List<CashflowComparer> hashes = FormHashesList(listTs, list1C, compareDate, db); // формируем список хэшей - сравнений платежей из 1С и ТС
WriteHashesDiffToDb(hashes, db); // актулизировать список по хэшам
}
List<CashflowComparer> FormHashesList(List<tbl_Cashflow> listTs, List<Transfer> list1C, DateTime compareDate, Entities db)
{
List<CashflowComparer> hashes = new List<CashflowComparer>();
foreach (tbl_Cashflow t in listTs)
{
string h = CashflowComparer.getHash(t);
CashflowComparer cc = new CashflowComparer()
{
hash = h,
objTs = t
};
hashes.Add(cc);
}
foreach (Transfer t in list1C)
{
tbl_Cashflow c = CreateCashflow(db, t, compareDate);
string h = CashflowComparer.getHash(c);
if (hashes.Exists(x => x.hash == h))
{
hashes.FirstOrDefault(x => x.hash == h).obj1C = c;
}
else
{
CashflowComparer cc = new CashflowComparer()
{
hash = h,
obj1C = c
};
hashes.Add(cc);
}
}
return hashes;
}
public static Guid? GetContractID(Entities db, string ContractNumber)
{
char[] alph_en = { 'E', 'T', 'O', 'P', 'A', 'H', 'K', 'X', 'C', 'B', 'M' };
char[] alph_ru = { 'Е', 'Т', 'О', 'Р', 'А', 'Н', 'К', 'Х', 'С', 'В', 'М' };
Guid? ContractID = null;
tbl_Contract a = db.tbl_Contract.FirstOrDefault(x => x.ContractNumber == ContractNumber);
if (a != null)
ContractID = a.ID;
else
{
for (int i = 0; i < alph_en.Length; i++)
{
if (ContractNumber.Contains(alph_en[i]))
{
string cn = ContractNumber.Replace(alph_en[i], alph_ru[i]);
a = db.tbl_Contract.FirstOrDefault(x => x.ContractNumber == cn);
if (a != null)
{
ContractID = a.ID;
///delete ExtraFound++;
break;
}
}
if (ContractNumber.Contains(alph_ru[i]))
{
string cn = ContractNumber.Replace(alph_ru[i], alph_en[i]);
a = db.tbl_Contract.FirstOrDefault(x => x.ContractNumber == cn);
if (a != null)
{
ContractID = a.ID;
///delete ExtraFound++;
break;
}
}
}
}
return ContractID;
}
public static Guid? GetOpportunityIDFromContract(Entities db, Guid? ContractID)
{
Guid? id = null;
id = db.tbl_Contract.FirstOrDefault(x => x.ID == ContractID).OpportunityID;
return id;
}
public static Guid? GetManagerIDFromAccount(Entities db, Guid? AccountID)
{
Guid? id = null;
id = db.tbl_Account.FirstOrDefault(x => x.ID == AccountID).OwnerID;
return id;
}
public static Guid? GetAccountIDFromContract(Entities db, Guid? ContractID)
{
Guid? id = null;
id = db.tbl_Contract.FirstOrDefault(x => x.ID == ContractID).CustomerID;
return id;
}
public tbl_Cashflow toTsCashflow(tbl_Cashflow p, Entities db)
{
tbl_Cashflow c = new tbl_Cashflow();
string comments = "";
c.Amount = decimal.Parse(this.Amount);
c.Obj1cDocNumIn = this.Number;
c.Subject = "Детализация: " + this.Contract;
c.OpportunityID = p.OpportunityID;
c.ManagerID = p.ManagerID;
c.OwnerID = p.OwnerID;
c.ActualDate = p.ActualDate;
c.AutocalcAmount = p.AutocalcAmount;
c.CampaignID = p.CampaignID;
c.CashAccountID = p.CashAccountID;
c.CategoryID = p.CategoryID;
c.CFNumber = p.CFNumber;
c.ClauseID = p.ClauseID;
c.CompanyID = p.CompanyID;
c.ContactID = p.ContactID;
c.CreatedByID = p.CreatedByID;
c.CreatedOn = DateTime.Now;
c.CurrencyID = p.CurrencyID;
c.CurrencyRate = p.CurrencyRate;
c.DebtorCreditorID = p.DebtorCreditorID;
c.Description = p.Description;
c.DocDate = p.DocDate;
c.EstimatedDate = p.EstimatedDate;
c.ExpactablePayDate = p.ExpactablePayDate;
c.ExpenseTypeID = p.ExpenseTypeID;
c.ID = Guid.NewGuid();
c.IncidentID = p.IncidentID;
//c.LinkID = p.LinkID;
c.MailSent = p.MailSent;
c.ModifiedByID = p.ModifiedByID;
c.ModifiedOn = DateTime.Now;
c.PayerID = p.PayerID;
c.PeriodID = p.PeriodID;
c.ProjectID = p.ProjectID;
c.RecipientID = p.RecipientID;
c.ServiceAgreementID = p.ServiceAgreementID;
c.StatusID = p.StatusID;
c.TypeID = p.TypeID;
c.UseAsCashflow = p.UseAsCashflow;
c.UseAsPandL = p.UseAsPandL;
//c.ContractID
//c.InvoiceID
string contractNumber = Helper.ParseContractNumber(this.Contract);
Guid? ContractID = DbHelper.GetContractID(db, contractNumber);
if (ContractID.HasValue)
{
c.ContractID = ContractID;
}
else
{
comments += "Договор не найден: " + this.Contract + ".";
}
// 29 ПРОДАЖА - получаем из договора
if (ContractID.HasValue)
{
c.OpportunityID = DbHelper.GetOpportunityIDFromContract(db, ContractID);
}
// 30 МЕНЕДЖЕР - получаем из договора
if (ContractID.HasValue)
{
c.ManagerID = DbHelper.GetManagerIDFromContract(db, ContractID);
}
else
if (c.PayerID.HasValue)
{
c.ManagerID = DbHelper.GetManagerIDFromAccount(db, c.PayerID);
}
else
{
c.ManagerID = DbHelper.GetDefaultManagerID();
}
string InvoiceString = "счет ";
string PayDetails = this.Contract;
if (PayDetails.Contains(InvoiceString))
{
int NumStart = PayDetails.IndexOf(InvoiceString) + 5;
string substr1 = PayDetails.Substring(NumStart);
int NumEnd = substr1.IndexOf(' ');
string InvoiceNumber = "";
if (NumEnd >= 0)
InvoiceNumber = substr1.Substring(0, NumEnd);
// найти счёт
// фильтр по номеру счёта
List<tbl_Invoice> invoices = db.tbl_Invoice.Where(i => i.InvoiceNumber == InvoiceNumber).ToList();
// фильтр по дате счёта (год)?
invoices = invoices.Where(i => i.InvoiceDate.Year == DateTime.Now.Year).ToList();
// фильтр по контрагенту
if (c.PayerID.HasValue)
{
invoices = invoices.Where(i => i.CustomerID == c.PayerID).ToList();
}
if (invoices.Count > 0)
{
tbl_Invoice i = invoices.First();
c.InvoiceID = i.ID;
}
}
c.Comments = comments;
c.UID1C = c.CFNumber;
// todo
//c.BasicAmount = p.BasicAmount;
return c;
}
void Update1cDocNumIns(Entities db, List<Transfer> list1C, List<tbl_Cashflow> lts, DateTime compareDate)
{
List<CashflowComparer> hashes = new List<CashflowComparer>();
List<tbl_Cashflow> listTs = lts.Where(x => String.IsNullOrEmpty(x.Obj1cDocNumIn)).ToList();
foreach (tbl_Cashflow c in listTs)
{
string h = CashflowComparer.getHashWithoutDocNum(c);
if (!hashes.Exists(x => x.hash == h))
{
CashflowComparer cc = new CashflowComparer()
{
hash = h,
objTs = c
};
hashes.Add(cc);
}
}
foreach (Transfer t in list1C)
{
tbl_Cashflow c = CreateCashflow(db, t, compareDate);
string h = CashflowComparer.getHashWithoutDocNum(c);
if (hashes.Exists(x => x.hash == h))
{
hashes.FirstOrDefault(x => x.hash == h).obj1C = c;
}
else
{
CashflowComparer cc = new CashflowComparer()
{
hash = h,
obj1C = c
};
hashes.Add(cc);
}
}
foreach (CashflowComparer cc in hashes)
{
if (cc.Action == ImportAction.UPDATE_1CDOCNUMIN)
{
//Console.WriteLine("Update 1C DocNumIn");
db.tbl_Cashflow.FirstOrDefault(x => x.ID == cc.objTs.ID).Obj1cDocNumIn = cc.obj1C.Obj1cDocNumIn;
}
}
db.SaveChanges();
}
public void RunExport(string connectionString, DateTime startDate, DateTime endDate, Company company)
{
string companyID = getDs_CompanyID(company);
using (Entities db = new Entities(connectionString)) // поднимаем подключение к БД
{
DateTime compareDate = startDate;
do
{
UpdateCashflowForDate(compareDate, companyID, db); // для каждой даты осуществляем сравнение выгруженнных из 1С и существующих в ТС платежей
compareDate = compareDate.AddDays(1);
} while (compareDate <= endDate); // проходим по всем датам из указанного в конфигурационном файле диапазона
}
}
public static Guid? GetContractID(Entities db, Transfer t)
{
Guid? ContractID = null;
Subconto Contract = t.Субконто2;
if (Contract != null)
{
string ContractNumber = Helper.ParseContractCode(Contract.Код);
ContractID = GetContractID(db, ContractNumber);
if (ContractID == null)
{
ContractNumber = Helper.ParseContractNumber(Contract.Наименование);
ContractID = GetContractID(db, ContractNumber);
}
}
return ContractID;
}
void WriteHashesDiffToDb(List<CashflowComparer> hashes, Entities db)
{
foreach (CashflowComparer cc in hashes)
{
switch (cc.Action)
{
case ImportAction.DELETE_FROM_TS: //Если хэш из набора-ТС отсутствует в наборе-1С - удаляем этот платёж из ТС
db.tbl_Cashflow.Remove(cc.objTs);
cc.PrintCompareResultInfo();
db.SaveChanges();
break;
case ImportAction.ADD_TO_TS: //Если хэш из набора-1с отсутствует в наборе-ТС - импортируем этот платёж из 1С в ТС
db.tbl_Cashflow.Add(cc.obj1C);
GrantToManagerAccessToCashflow(cc, db); // раздаём права на новый платёж его менеджеру
cc.PrintCompareResultInfo();
db.SaveChanges();
break;
}
}
}
public static Guid? GetAccountID(Entities db, Transfer t)
{
Guid? AccountID = null;
Subconto Account = t.Субконто1;
if (Account != null)
{
string AccountUNN = Account.Код;
AccountUNN = AccountUNN.ToLower().Replace('o', '0').Replace('о', '0'); // заменяем русскую и латинскую букву О на НОЛЬ
AccountID = DbHelper.GetAccountID(db, AccountUNN);
}
return AccountID;
}
public void ExportToLocalListV2(string connectionstring)
{
using (Entities db = new Entities(connectionstring))
{
Helper.Log("Соединение с базой установлено.");
foreach (BankStatement statement in BankStatements)
{
foreach (Transfer transfer in statement.Transfers)
{
CommitTransferToLocalList(db, transfer, statement);
}
}
Helper.Log("Экспорт в БД Terrasoft завершён.\n");
}
}
public static Guid? GetAccountID(Entities db, string AccountUNN)
{
Guid? AccountID = null;
tbl_Account a = db.tbl_Account.FirstOrDefault(x => x.Code == AccountUNN);
if (a != null)
AccountID = a.ID;
return AccountID;
}
public tbl_Cashflow CreateCashflow(Entities db, Transfer t)
{
tbl_Cashflow c = new tbl_Cashflow();
c.UID1C = t.UID1C;
//TMP
c.MailSent = 1;
c.Obj1cDocNumIn = t.НомерДокВходящий;
c.ModifiedByID = Helper.GetSupervisorID();
c.ModifiedOn = DateTime.Now;
c.CreatedByID = Helper.GetSupervisorID();
c.CreatedOn = DateTime.Now;
string cID = Helper.getDs_CompanyID(t.Company);
c.CompanyID = new Guid(cID);
// 01 НОМЕР
c.CFNumber = Helper.GetNextNumber(db);
// 02 НАЗНАЧЕНИЕ
c.Subject = t.НазначениеПлатежа;
if(t.ВидДвижения != null)
{
if(t.ВидДвижения.Наименование.Length>0)
c.Subject += " (" + t.ВидДвижения.Наименование + ")";
}
if(c.Subject.Length > 249)
{
c.Subject = c.Subject.Substring(0, 246) + "...";
}
// 03 ОТ
c.DocDate = DateTime.Parse(t.ДатаДок);
// 04 ТИП
CashflowType cashflowType;
if (t.Приход > 0)
cashflowType = CashflowType.Income;
else
cashflowType = CashflowType.Expense;
c.TypeID = Helper.GetCashflowTypeID(cashflowType);
// 05 СТАТЬЯ NULL
c.ClauseID = DbHelper.GetCashflowClauseID(db, t.ВидДвижения, t.Company);
// 06 КАТЕГОРИЯ NULL
// 07 ОТВЕТСТВЕННЫЙ
c.OwnerID = Helper.GetOwnerID();
// 08 СОСТОЯНИЕ
c.StatusID = new Guid(Constants.CashflowStateFinishedID);
// 09 ИНЦИДЕНТ NULL
// 10 ВОЗДЕЙСТВИЕ NULL
// 11 PL - (UseAsPandL - P&L) NULL
// 12 КАССА
c.CashAccountID = new Guid(Constants.CashflowKassaID);
// 13 ПЛАНИРУЕМАЯ ДАТА
c.EstimatedDate = DateTime.Parse(t.ДатаДок);
// 14 ФАКТИЧЕСКАЯ ДАТА
c.ActualDate = DateTime.Parse(t.ДатаДок);
// 15 ТИП РАСХОДА-ДОХОДА NULL
// 16 ПЕРИОД
c.PeriodID = Helper.GetPeriodID(db, c.ActualDate.Value);
// 00 Контрагент
string comments = "";
Guid? AccountID = DbHelper.GetAccountID(db, t);
if (!AccountID.HasValue)
{
comments += "Контрагент не найден: " + Helper.GetAccountNameCode(t) + ".\r\n";
}
// 19 УЧИТЫВАТЬ ПРИ ВЗАИМОРАСЧЁТАХ NULL
// 20 ДЕБИТОР-КРЕДИТОР NULL
// 21 АВТОМАТИЧЕСКИ РАССЧИТЫВАТЬ СУММУ
c.AutocalcAmount = 1;
// 22 ВАЛЮТ
c.CurrencyID = DbHelper.ConvertToCurrencyID(t.Валюта);
// 23 СУММА
if (cashflowType == CashflowType.Income)
c.Amount = (decimal)t.Приход;
else
c.Amount = -1 * (decimal)t.Расход;
// 24 ВНУТРЕННИЙ КУРС
c.CurrencyRate = (decimal)t.Курс;
// 25 СУММА В БАЗОВОЙ ВАЛЮТЕ NULL
//c.BasicAmount = (int)(c.Amount / c.CurrencyRate);
// 26 КОНТАКТ NULL
// 27 СЧЁТ NULL
// 28 ДОГОВОР
Guid? ContractID = DbHelper.GetContractID(db, t);
if (ContractID.HasValue)
{
c.ContractID = ContractID;
}
else
{
string ContractName = DbHelper.GetContractName(t);
if(!String.IsNullOrEmpty(ContractName))
comments += "Договор не найден: " + ContractName + ".";
}
// 29 ПРОДАЖА - получаем из договора
if (ContractID.HasValue)
{
c.OpportunityID = DbHelper.GetOpportunityIDFromContract(db, ContractID);
}
// 30 МЕНЕДЖЕР - получаем из договора
if (ContractID.HasValue)
{
c.ManagerID = DbHelper.GetManagerIDFromContract(db, ContractID);
}
else
if (AccountID.HasValue)
{
c.ManagerID = DbHelper.GetManagerIDFromAccount(db, AccountID);
}
else
{
c.ManagerID = DbHelper.GetDefaultManagerID();
}
// 31 если найден договор, но не найден контрагент
if (ContractID.HasValue && !AccountID.HasValue)
{
AccountID = DbHelper.GetAccountIDFromContract(db, ContractID);
}
// 17 ПЛАТЕЛЬЩИК
// 18 ПОЛУЧАТЕЛЬ
if (cashflowType == CashflowType.Income)
{
c.PayerID = AccountID;
c.RecipientID = DbHelper.GetCompanyID(t.Company);
}
else
{
c.PayerID = DbHelper.GetCompanyID(t.Company);
c.RecipientID = AccountID;
}
c.DebtorCreditorID = c.PayerID;
// если назначение содержит номер счёта
string PayDetails = t.НазначениеПлатежа;
string InvoiceString = "счет ";
if (PayDetails.Contains(InvoiceString))
{
int NumStart = PayDetails.IndexOf(InvoiceString) + 5;
string substr1 = PayDetails.Substring(NumStart);
int NumEnd = substr1.IndexOf(' ');
string InvoiceNumber = "";
if (NumEnd >= 0)
InvoiceNumber = substr1.Substring(0, NumEnd);
// найти счёт
// фильтр по номеру счёта
List<tbl_Invoice> invoices = db.tbl_Invoice.Where(i => i.InvoiceNumber == InvoiceNumber).ToList();
// фильтр по дате счёта (год)?
invoices = invoices.Where(i => i.InvoiceDate.Year == DateTime.Now.Year).ToList();
// фильтр по контрагенту
if (AccountID.HasValue)
{
invoices = invoices.Where(i => i.CustomerID == AccountID).ToList();
}
if (invoices.Count > 0)
{
tbl_Invoice i = invoices.First();
c.InvoiceID = i.ID;
}
}
if (comments.Length > 249)
{
comments = comments.Substring(0, 246) + "...";
}
c.Comments = comments;
//t.КоррСчёт - пока не использовать
c.ID = Guid.NewGuid();
return c;
}
public static Guid? GetManagerIDFromContract(Entities db, Guid? ContractID)
{
Guid? id = null;
tbl_Contract contract = db.tbl_Contract.FirstOrDefault(x => x.ID == ContractID);
id = contract.OwnerID;
return id;
}
List<tbl_Cashflow> getTransfersFromTs(DateTime compareDate, string companyID, Entities db)
{
Guid ds_compID = new Guid(companyID);
//Console.WriteLine(compareDate.ToShortDateString());
List<tbl_Cashflow> listTs = db.tbl_Cashflow.Where(x => x.ActualDate == compareDate
&& (x.CFNumber.StartsWith("1С") || x.CFNumber.StartsWith("1C"))
&& x.CompanyID == ds_compID).OrderBy(x => x.Amount).ToList();
return listTs;
}
private void CommitTransferToDatabase(Entities db, Transfer t, string docDate)
{
tbl_Cashflow c = CreateCashflow(db, t, docDate);
db.tbl_Cashflow.Add(c);
db.SaveChanges();
}
void UpdateCashflowForDate(List<BankStatement> statements, DateTime compareDate, string companyID, Entities db)
{
Console.Write("\n{0} \t", compareDate.ToShortDateString());
List<Transfer> list1C = getTransfersFrom1c(statements, compareDate); // выбираем платежи из выписки 1С на дату
var uids1C = list1C.Select(x => x.UID1C);
List<tbl_Cashflow> listTs = getTransfersFromTs(compareDate, companyID, db); // выбираем платежи из ТС на дату
var uidsTs = listTs.Select(x => x.UID1C);
// обновим общие идентификаторы
var uidsToUpdate = uids1C.Intersect(uidsTs);
int changeflag = 0;
foreach(string uidToCompare in uidsToUpdate)
{
tbl_Cashflow c1C = CreateCashflow(db, list1C.FirstOrDefault(x => x.UID1C == uidToCompare));
tbl_Cashflow cTS = listTs.FirstOrDefault(x => x.UID1C == uidToCompare);
// check for changes
bool changed = false;
// Amount
if(c1C.Amount != cTS.Amount)
{
cTS.Amount = c1C.Amount;
changed = true;
}
// PayerID
if (c1C.PayerID != cTS.PayerID)
{
cTS.PayerID = c1C.PayerID;
changed = true;
}
// RecipientID
if (c1C.RecipientID != cTS.RecipientID)
{
cTS.RecipientID = c1C.RecipientID;
changed = true;
}
// ContractID
if (c1C.ContractID != cTS.ContractID)
{
cTS.ContractID = c1C.ContractID;
changed = true;
}
if (changed)
changeflag++;
}
if(changeflag > 0)
{
db.SaveChanges();
}
Console.Write("Updated: {0}/{1} \t", changeflag, uidsToUpdate.Count());
// удалим левые идентификаторы
var cashflowsToDelete = listTs.Where(x => !uids1C.Contains(x.UID1C));
if (cashflowsToDelete.Count() > 0)
{
Console.Write("Delete: {0} \t", cashflowsToDelete.Count());
db.tbl_Cashflow.RemoveRange(cashflowsToDelete);
db.SaveChanges();
}
// добавим новые
var cashflowsToInsert = list1C.Where(x => !uidsTs.Contains(x.UID1C));
foreach(Transfer t in cashflowsToInsert)
{
tbl_Cashflow newCashflow = CreateCashflow(db, t);
db.tbl_Cashflow.Add(newCashflow);
DbHelper.GrantToManagerAccessToCashflow(newCashflow, db); // раздаём права на новый платёж его менеджеру
db.SaveChanges();
}
if (cashflowsToInsert.Count() > 0)
{
Console.Write("Insert: {0}", cashflowsToInsert.Count());
db.SaveChanges();
}
}
private string GetNextNumber(Entities db)
{
string num = "1С" + (db.tbl_Cashflow.Count() + this.cashflowCount++ + 1).ToString();
return num;
}
public static string GetNextNumber(Entities db)
{
string num = "1С" + (db.tbl_Cashflow.Count() + cashflowCount++ + 1).ToString();
return num;
}
private Guid? GetAccountID(Entities db, Transfer t)
{
Guid? AccountID = null;
Subconto Account = GetSubconto(t, SubcontoType.Контрагент);
if (Account != null)
{
string AccountUNN = Account.Код;
AccountID = GetAccountID(db, AccountUNN);
}
return AccountID;
}
