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 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();
}
}
}
}
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();
}
}
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;
}
}
}
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();
}
private void CommitTransferToDatabase(Entities db, Transfer t, string docDate)
{
tbl_Cashflow c = CreateCashflow(db, t, docDate);
db.tbl_Cashflow.Add(c);
db.SaveChanges();
}
private void CommitTransferToDatabase(Entities db, Transfer t, BankStatement statement)
{
tbl_Cashflow c = CreateCashflow(db, t, statement);
db.AddTotbl_Cashflow(c);
db.SaveChanges();
}