protected virtual void FillDatabase(Session ssn)
{
department1 = Prepare_hrmDepartment(ssn, "1");
department2 = Prepare_hrmDepartment(ssn, "2");
country1 = Prepare_csCountry(ssn, "1");
address1 = Prepare_csAddress(ssn, "1", country1);
address2 = Prepare_csAddress(ssn, "1", country1);
contractCategory1 = Prepare_crmContractCategory(ssn, "1");
documentCategory1 = Prepare_crmDocumentCategory(ssn, "1");
legalPersonRussianRu1 = Prepare_crmCLegalPerson(ssn, "1", address1);
legalPersonRussianRu2 = Prepare_crmCLegalPerson(ssn, "2", address2);
partyRu1 = legalPersonRussianRu1.Party; // Prepare_crmPartyRu(ssn, "1", address1, legalPersonRussianRu1);
partyRu2 = legalPersonRussianRu2.Party; // Prepare_crmPartyRu(ssn, "2", address2, legalPersonRussianRu2);
unit1 = Prepare_csUnit(ssn, "1");
nomenclatureType1 = Prepare_csNomenclatureType(ssn, "1");
costItem1 = Prepare_fmCostItem(ssn, "1");
valuta1 = Prepare_csValuta(ssn, "1", nomenclatureType1, unit1, costItem1);
physicalPersonRu1 = Prepare_crmPhysicalPerson(ssn, "1", address1);
physicalPersonRu2 = Prepare_crmPhysicalPerson(ssn, "1", address2);
staff1 = Prepare_hrmStaff(ssn, "1", physicalPersonRu1, department1);
staff2 = Prepare_hrmStaff(ssn, "2", physicalPersonRu2, department2);
}
/// <summary>
/// Дата изменения счёта (в зависимости от того, какая сторона является "нашей")
/// </summary>
/// <returns></returns>
public DateTime GetAccountDateChange()
{
crmCParty OurParty = GetOurParty();
DateTime AccountDateChanged = DeductedFromPayerAccount;
if (this.PaymentPayerRequisites.Party == OurParty)
{
//return DeductedFromPayerAccount;
AccountDateChanged = DeductedFromPayerAccount;
}
else
{
//return ReceivedByPayerBankDate;
AccountDateChanged = ReceivedByPayerBankDate;
}
// Если дата не определилась (редкий случай)
if (AccountDateChanged == DateTime.MinValue)
{
AccountDateChanged = DeductedFromPayerAccount;
}
if (AccountDateChanged == DateTime.MinValue)
{
AccountDateChanged = ReceivedByPayerBankDate;
}
return(AccountDateChanged);
}
static public fmCAVTBookVAT LocateBook(IObjectSpace os, crmCParty organ, fmCAVTBookVAT.fmCAVTBookVATType type, String period)
{
IList <fmCAVTBookVAT> books = os.GetObjects <fmCAVTBookVAT>(
XPQuery <fmCAVTBookVAT> .TransformExpression(((ObjectSpace)os).Session,
b => b.Party == organ &&
b.Period == period), true).ToList();
fmCAVTBookVAT book_main = null;
foreach (fmCAVTBookVAT book in books)
{
if ((type == fmCAVTBookVAT.fmCAVTBookVATType.PAY_MAIN ||
type == fmCAVTBookVAT.fmCAVTBookVATType.PAY_ADD) &&
book.BookVATType == fmCAVTBookVAT.fmCAVTBookVATType.PAY_MAIN)
{
book_main = book;
break;
}
if ((type == fmCAVTBookVAT.fmCAVTBookVATType.BAY_MAIN ||
type == fmCAVTBookVAT.fmCAVTBookVATType.BAY_ADD) &&
book.BookVATType == fmCAVTBookVAT.fmCAVTBookVATType.BAY_MAIN)
{
book_main = book;
break;
}
}
if (book_main == null)
{
if (type == fmCAVTBookVAT.fmCAVTBookVATType.PAY_MAIN ||
type == fmCAVTBookVAT.fmCAVTBookVATType.PAY_ADD)
{
}
}
return(book_main);
}
//
public override IEnumerator <gcrCodifDeal> GetEnumerator()
{
crmCParty user_party = null;
if (crmUserParty.CurrentUserParty != null)
{
if (crmUserParty.CurrentUserParty.Value != null)
{
user_party = (crmCParty)crmUserParty.CurrentUserPartyGet(this.session).Party;
}
}
XPCollection <crmContractDeal> deals = new XPCollection <crmContractDeal>(
PersistentCriteriaEvaluationBehavior.BeforeTransaction, this.session,
GroupOperator.Or(
new BinaryOperator("Current.Customer.Party", user_party, BinaryOperatorType.Equal),
new BinaryOperator("Current.Supplier.Party", user_party, BinaryOperatorType.Equal)
)
);
// IList<gcrCodifDeal> result = new List<gcrCodifDeal>(10000);
// foreach (crmContractDeal ldeal in deals) {
// result.Add(new gcrCodifDeal(this.session, ldeal.Contract, user_party, ldeal));
// }
// foreach ()
// return GetQuery().GetEnumerator();
return(deals.Select <crmContractDeal, gcrCodifDeal>(
x => new gcrCodifDeal(this.session, x.Contract, user_party, x)
).GetEnumerator());
}
public void ClearFilter()
{
this.DateRegBegin = default(DateTime);
this.DateRegEnd = default(DateTime);
this.DateInvoiceBegin = default(DateTime);
this.DateInvoiceEnd = default(DateTime);
this.InvoiceNumber = "";
this.State = 0;
Receiver = null;
PaymentRequest = null;
Comment = "";
}
/// <summary>
/// Валюта счёта (в зависимости от того, какая сторона является "нашей")
/// </summary>
/// <returns></returns>
public csValuta GetAccountValuta()
{
crmCParty OurParty = GetOurParty();
if (this.PaymentPayerRequisites.Party == OurParty)
{
return(crmBankAccount.GetValutaByBankAccount(Session, PaymentPayerRequisites.BankAccount));
}
else
{
return(crmBankAccount.GetValutaByBankAccount(Session, PaymentReceiverRequisites.BankAccount));
}
}
/// <summary>
/// Исходящая сумма (относительно "нашей" организации)
/// </summary>
/// <returns></returns>
public Decimal GetSumOut(crmBankAccount bankAccount)
{
Decimal Res = 0;
// Наша организация
crmCParty OurParty = GetOurParty();
if (this.PaymentPayerRequisites.INN == OurParty.INN && this.PaymentPayerRequisites.StatementOfAccount.BankAccount == bankAccount)
{
Res = this.PaymentCost;
}
return(Res);
}
private crmCParty GetOurParty(Session ssn)
{
// Наша организация
crmCParty pOurParty = null;
if (crmUserParty.CurrentUserParty != null)
{
if (crmUserParty.CurrentUserParty.Value != null)
{
pOurParty = (crmCParty)crmUserParty.CurrentUserPartyGet(ssn).Party;
}
}
return(pOurParty);
}
private crmCParty GetOurParty()
{
// Наша организация
crmCParty OurParty = null;
if (crmUserParty.CurrentUserParty != null)
{
if (crmUserParty.CurrentUserParty.Value != null)
{
OurParty = (crmCParty)crmUserParty.CurrentUserPartyGet(this.Session).Party;
}
}
return(OurParty);
}
private crmCParty GetOurPartyByCode(UnitOfWork uow, string BuhCode)
{
crmCParty partyRes = null;
XPQuery <crmCParty> parties = new XPQuery <crmCParty>(uow);
var queryParties = from party in parties
where party.Code == BuhCode
select party;
foreach (var party in queryParties)
{
partyRes = party;
break;
}
return(partyRes);
}
//
static crmCParty GetParty(Session ses, String code)
{
crmCParty party = null;
XPQuery <crmCParty> partys = new XPQuery <crmCParty>(ses);
var qc = from pe in partys
where pe.Code == code
select pe;
foreach (crmCParty pi in qc)
{
party = pi;
break;
}
return(party);
}
protected crmContractDeal DealCheck(crmCParty party, String cnt_number, DateTime cnt_date)
{
XPQuery <crmContractDeal> deals = new XPQuery <crmContractDeal>(this.Session);
var qd = from deal in deals
where
deal.Customer == party &&
deal.ContractDocument.Number == cnt_number &&
deal.ContractDocument.Date.Date == cnt_date.Date
select deal;
foreach (crmContractDeal deal in qd)
{
return(deal);
}
return(null);
}
public static TrwPartyParty LocateTrwParty(IObjectSpace os, crmCParty party)
{
if (party.Person == null)
{
return(null);
}
TrwPartyParty trw_party = party.Person.TrwParty;
// trw_partys.FirstOrDefault(x => x.Person == party.Person);
if (trw_party == null)
{
trw_party = os.CreateObject <TrwPartyParty>();
trw_party.Person = party.Person;
trw_party.Party = party;
}
return(trw_party);
}
static public fmCAVTBookVAT LocateBookAdd(IObjectSpace os, crmCParty organ, fmCAVTBookVAT.fmCAVTBookVATType type, String period)
{
if (type != fmCAVTBookVAT.fmCAVTBookVATType.PAY_ADD &&
type != fmCAVTBookVAT.fmCAVTBookVATType.BAY_ADD)
{
throw new ArgumentException();
}
fmCAVTBookVAT book_main = null;
if (type == fmCAVTBookVAT.fmCAVTBookVATType.PAY_ADD)
{
book_main = LocateBookMain(os, organ, fmCAVTBookVAT.fmCAVTBookVATType.PAY_MAIN, period);
}
if (type == fmCAVTBookVAT.fmCAVTBookVATType.BAY_ADD)
{
book_main = LocateBookMain(os, organ, fmCAVTBookVAT.fmCAVTBookVATType.BAY_MAIN, period);
}
return(book_main);
}
protected fmCAVTInvoiceBase AVTInvoiceCheck()
{
crmCParty supl = null;
if (crmUserParty.CurrentUserParty != null)
{
if (crmUserParty.CurrentUserParty.Value != null)
{
supl = (crmCParty)crmUserParty.CurrentUserPartyGet(this.Session).Party;
}
}
fmCAVTInvoiceBase invoice = Session.FindObject <fmCAVTInvoiceBase>(
CriteriaOperator.And(
new BinaryOperator("Number", AVTInvoiceNumber),
new BinaryOperator("Date", AVTInvoiceDate),
new BinaryOperator("Supplier", supl)
));
return(invoice);
}
static public fmCAVTBookVAT LocateBookMain(IObjectSpace os, crmCParty organ, fmCAVTBookVAT.fmCAVTBookVATType type, String period)
{
if (type != fmCAVTBookVAT.fmCAVTBookVATType.PAY_MAIN &&
type != fmCAVTBookVAT.fmCAVTBookVATType.BAY_MAIN)
{
throw new ArgumentException();
}
fmCAVTBookVAT book = os.FindObject <fmCAVTBookVAT>(
XPQuery <fmCAVTBookVAT> .TransformExpression(((ObjectSpace)os).Session,
b => b.Party == organ &&
b.BookVATType == type &&
b.Period == period), true);
if (book == null)
{
book = os.CreateObject <fmCAVTBookVAT>();
book.Party = organ;
book.BookVATType = type;
book.Period = period;
}
return(book);
}
public override IQueryable <gcrCodifDeal> GetQuery()
{
// XPQuery<crmContractDeal> deals = new XPQuery<crmContractDeal>(ses);
crmCParty user_party = null;
if (crmUserParty.CurrentUserParty != null)
{
if (crmUserParty.CurrentUserParty.Value != null)
{
user_party = (crmCParty)crmUserParty.CurrentUserPartyGet(this.session).Party;
}
}
var query = from ldeal in Provider
// where ldeal.Current.Customer.Party == user_party ||
// ldeal.Current.Supplier.Party == user_party
select new gcrCodifDeal(this.session, ldeal.Contract,
user_party,
ldeal);
return(query);
}
public gcrCodifDeal(Session ses, crmContract contract, crmCParty our_party, crmContractDeal deal) : base(ses)
{
_Contract = contract;
_Deal = deal;
crmCParty cust_party = null;
crmCParty supl_party = null;
if (deal.Current.Customer != null)
{
cust_party = deal.Current.Customer.Party;
}
if (deal.Current.Supplier != null)
{
supl_party = deal.Current.Supplier.Party;
}
if (cust_party != our_party)
{
_Party = cust_party;
}
else
{
_Party = supl_party;
}
}
//
static void Main(string[] args)
{
Session bses = GetSession();
int i = 1;
if (args[0] == "city")
{
using (UnitOfWork ses = bses.BeginNestedUnitOfWork()) {
XPQuery <csAddress> aq = new XPQuery <csAddress>(ses);
var q = from address in aq
where (address.City == null ||
address.City == "" ||
address.City == " ") &&
!(address.AddressString == null ||
address.AddressString == "" ||
address.AddressString == " ")
select address;
foreach (csAddress address in q)
{
System.Console.WriteLine(address.AddressString + " : " +
address.AddressHandmake + " : " +
address.City);
address.City = "Не задан";
}
System.Console.WriteLine(q.Count());
ses.CommitChanges();
}
}
if (args[0] == "party")
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
xlApp = new Excel.ApplicationClass();
// Открытие документа (книги) excel
xlWorkBook = xlApp.Workbooks.Open("e:\\party2.xls");
// Выбирается Лист (если существует)
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//
//try {
while (true)
{
using (UnitOfWork ses = bses.BeginNestedUnitOfWork()) {
i++;
ExcelParty ep = new ExcelParty();
// Excel.Range range = xlWorkSheet.get_Range("A" + i, "P" + i);
// Excel.Range range2 = xlWorkSheet.get_Range("A1");
if (xlWorkSheet.get_Range("A" + i).Value2 == null)
{
break;
}
ep.code = xlWorkSheet.get_Range("A" + i).Value2.ToString();
ep.type = xlWorkSheet.get_Range("B" + i).Value2.ToString();
if (xlWorkSheet.get_Range("C" + i).Value2 != null)
{
ep.close = xlWorkSheet.get_Range("C" + i).Value2.ToString();
}
else
{
ep.close = String.Empty;
}
if (xlWorkSheet.get_Range("E" + i).Value2 != null)
{
ep.country = UpFirstCase(xlWorkSheet.get_Range("E" + i).Value2.ToString());
}
else
{
ep.country = String.Empty;
}
if (xlWorkSheet.get_Range("F" + i).Value2 != null)
{
ep.city = UpFirstCase(xlWorkSheet.get_Range("F" + i).Value2.ToString());
}
else
{
ep.city = "-";
}
if (xlWorkSheet.get_Range("G" + i).Value2 != null)
{
ep.legal = xlWorkSheet.get_Range("G" + i).Value2.ToString();
}
else
{
ep.legal = String.Empty;
}
if (xlWorkSheet.get_Range("H" + i).Value2 != null)
{
ep.lpt = xlWorkSheet.get_Range("H" + i).Value2.ToString();
}
else
{
ep.lpt = "-";
}
ep.inn = xlWorkSheet.get_Range("I" + i).Value2.ToString();
if (xlWorkSheet.get_Range("J" + i).Value2 != null)
{
ep.kpp = xlWorkSheet.get_Range("J" + i).Value2.ToString();
}
else
{
ep.kpp = String.Empty;
}
ep.name = xlWorkSheet.get_Range("K" + i).Value2.ToString();
ep.addr = xlWorkSheet.get_Range("L" + i).Value2.ToString();
// party.NameFull = party.Name;
// party.AddressFact.AddressHandmake = xlWorkSheet.get_Range("L" + i, "L" + i).Value2.ToString();
// party.INN = xlWorkSheet.get_Range("I" + i, "I" + i).Value2.ToString();
// party.KPP = xlWorkSheet.get_Range("J" + i, "J" + i).Value2.ToString();
switch (ep.type)
{
case "ЛЮ":
crmCLegalPerson lp = NewLegalPerson(ses, ep);
//lp.Save();
break;
case "ЛФ":
crmCPhysicalParty php = new crmCPhysicalParty(ses);
php.Code = ep.code;
php.NameHandmake = ep.name;
php.INN = ep.inn;
php.AddressLegal.Country = GetCountry(ses, ep.country);
php.AddressLegal.City = ep.city;
php.AddressLegal.AddressHandmake = ep.addr;
php.AddressFact.Country = php.AddressLegal.Country;
php.AddressFact.City = ep.city;
php.AddressFact.AddressHandmake = ep.addr;
if (!String.IsNullOrEmpty(ep.close.Trim()))
{
php.IsClosed = true;
}
//php.Save();
break;
case "ИП":
crmCBusinessman bp = new crmCBusinessman(ses);
bp.Code = ep.code;
bp.NameHandmake = ep.name;
bp.INN = ep.inn;
bp.PersonType = GetPersonType(ses, ep.lpt);
bp.AddressLegal.Country = GetCountry(ses, ep.country);
bp.AddressLegal.City = ep.city;
bp.AddressLegal.AddressHandmake = ep.addr;
bp.AddressFact.Country = bp.AddressLegal.Country;
bp.AddressFact.City = ep.city;
bp.AddressFact.AddressHandmake = ep.addr;
if (!String.IsNullOrEmpty(ep.close.Trim()))
{
bp.IsClosed = true;
}
//bp.Save();
break;
case "ФИЛИАЛ":
crmCLegalPersonUnit lpu = UpdateLegalPersonUnit(ses, ep);
//lpu.Save();
break;
case "ПРОЧИЕ":
crmCParty party2 = new crmCParty(ses);
party2.Code = ep.code;
party2.Name = ep.name;
party2.INN = ep.inn;
party2.AddressFact.Country = GetCountry(ses, ep.country);
party2.AddressFact.City = ep.city;
party2.AddressFact.AddressHandmake = ep.addr;
if (!String.IsNullOrEmpty(ep.close.Trim()))
{
party2.IsClosed = true;
}
//party2.Save();
break;
case "ДОМ":
crmCParty party = new crmCParty(ses);
party.Code = ep.code;
party.Name = ep.name;
party.INN = ep.inn;
party.AddressFact.Country = GetCountry(ses, ep.country);
party.AddressFact.City = ep.city;
party.AddressFact.AddressHandmake = ep.addr;
if (!String.IsNullOrEmpty(ep.close.Trim()))
{
party.IsClosed = true;
}
//party.Save();
break;
default:
throw new NotSupportedException();
}
//MessageBox.Show(xlWorkSheet.get_Range("B1", "B1").Value2.ToString());
System.Console.WriteLine(ep.type + ' ' + ep.code + ' ' + ep.name + ' ' + ep.addr);
ses.CommitChanges();
}
}
//}
//catch (Exception e) {
// if (xlWorkSheet != null) releaseObject(xlWorkSheet);
// if (xlWorkBook != null) releaseObject(xlWorkBook);
// if (xlApp != null) releaseObject(xlApp);
// xlWorkSheet = null;
// xlWorkBook = null;
// xlApp = null;
// GC.Collect();
// System.Console.WriteLine(e);
// throw new Exception("Error clear", e);
// }
// Закрытие книги
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
if (args[0] == "dogovor")
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
xlApp = new Excel.ApplicationClass();
// Открытие документа (книги) excel
xlWorkBook = xlApp.Workbooks.Open("e:\\Договора.xlsx");
// Выбирается Лист (если существует)
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//
try {
while (true)
{
using (UnitOfWork ses = bses.BeginNestedUnitOfWork()) {
i++;
ExcelDogovor dog = new ExcelDogovor();
// Excel.Range range = xlWorkSheet.get_Range("A" + i, "P" + i);
// Excel.Range range2 = xlWorkSheet.get_Range("A1");
if (xlWorkSheet.get_Range("A" + i).Value2 == null)
{
break;
}
dog.staff_code = xlWorkSheet.get_Range("A" + i).Value2.ToString();
dog.dep_code = xlWorkSheet.get_Range("B" + i).Value2.ToString();
dog.type = xlWorkSheet.get_Range("C" + i).Value2.ToString();
dog.dog_id = xlWorkSheet.get_Range("F" + i).Value2.ToString();
dog.dog_num = xlWorkSheet.get_Range("G" + i).Value2.ToString();
String str_date = xlWorkSheet.get_Range("H" + i).Value2.ToString();
dog.dog_date = DateTime.Parse(str_date.Substring(0, 4) + "." +
str_date.Substring(4, 2) + "." +
str_date.Substring(6, 2));
if (xlWorkSheet.get_Range("I" + i).Value2 != null)
{
dog.dop_id = xlWorkSheet.get_Range("I" + i).Value2.ToString();
dog.dop_num = xlWorkSheet.get_Range("J" + i).Value2.ToString();
str_date = xlWorkSheet.get_Range("K" + i).Value2.ToString();
dog.dop_date = DateTime.Parse(str_date.Substring(0, 4) + "." +
str_date.Substring(4, 2) + "." +
str_date.Substring(6, 2));
//dog.dop_date = (DateTime)xlWorkSheet.get_Range("K" + i).Value2;
}
dog.customer_code = xlWorkSheet.get_Range("L" + i).Value2.ToString();
dog.supplier_code = xlWorkSheet.get_Range("P" + i).Value2.ToString();
crmCParty cust = GetParty(ses, dog.customer_code);
if (cust == null)
{
new KeyNotFoundException(dog.customer_code);
}
crmCParty supl = GetParty(ses, dog.supplier_code);
if (supl == null)
{
new KeyNotFoundException(dog.supplier_code);
}
dog.dep = GetDepartment(ses, dog.dep_code);
if (dog.dep == null)
{
new KeyNotFoundException(dog.dep_code);
}
dog.reg_dep = GetDepartment(ses, "00-056");
if (dog.reg_dep == null)
{
new KeyNotFoundException("00-056");
}
dog.reg_staff = GetStaff(ses, dog.staff_code);
if (dog.reg_staff == null)
{
new KeyNotFoundException(dog.staff_code);
}
crmContract contract = GetContract(ses, dog);
crmContractDeal deal = new crmDealWithStage(ses);
deal.State = DealStates.DEAL_FORMATION;
deal.CuratorDepartment = dog.dep;
deal.UserRegistrator = dog.reg_staff;
deal.DepartmentRegistrator = dog.reg_dep;
deal.Customer = cust;
deal.Supplier = supl;
contract.ContractDeals.Add(deal);
if (dog.type == "ДОГ")
{
deal.ContractKind = ContractKind.CONTRACT;
deal.ContractDocument = contract.ContractDocument;
}
else
{
deal.ContractKind = ContractKind.ADDENDUM;
crmContractDocument contract_document = new crmContractDocument(ses);
contract_document.DocumentCategory = GetDocumentCategory(ses, "ДС");
contract_document.Number = dog.dop_num;
contract_document.Date = dog.dop_date;
deal.ContractDocument = contract_document;
contract.ContractDocuments.Add(contract_document);
}
System.Console.WriteLine(dog.type + ' ' + dog.dog_id + ' ' + dog.dog_num + ' ' + dog.dog_date + ' ' + dog.customer_code + ' ' + dog.supplier_code);
ses.CommitChanges();
}
}
}
catch (Exception e) {
if (xlWorkSheet != null)
{
releaseObject(xlWorkSheet);
}
if (xlWorkBook != null)
{
releaseObject(xlWorkBook);
}
if (xlApp != null)
{
releaseObject(xlApp);
}
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;
GC.Collect();
System.Console.WriteLine(e);
throw e;
}
// Закрытие книги
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
if (args[0] == "subject")
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
xlApp = new Excel.ApplicationClass();
// Открытие документа (книги) excel
xlWorkBook = xlApp.Workbooks.Open("e:\\subject.xlsx");
// Выбирается Лист (если существует)
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//
try {
while (true)
{
using (UnitOfWork ses = bses.BeginNestedUnitOfWork()) {
i++;
FinanceTema ft = new FinanceTema();
if (xlWorkSheet.get_Range("A" + i).Value2 == null)
{
break;
}
ft.dir = xlWorkSheet.get_Range("A" + i).Value2.ToString();
ft.code = xlWorkSheet.get_Range("B" + i).Value2.ToString();
ft.name = xlWorkSheet.get_Range("C" + i).Value2.ToString();
if (xlWorkSheet.get_Range("D" + i).Value2 != null)
{
ft.desc = xlWorkSheet.get_Range("D" + i).Value2.ToString();
}
else
{
ft.desc = ft.name;
}
fmCDirection dir = GetObject <fmCDirection>(ses, ft.dir);
fmCSubjectExt subj = GetObject <fmCSubjectExt>(ses, ft.code);
if (subj == null)
{
subj = new fmCSubjectExt(ses);
}
subj.Code = ft.code;
subj.Direction = dir;
subj.Name = ft.name;
subj.NameFull = ft.name;
subj.Description = ft.desc;
if (subj.Name.Length > 80)
{
subj.Name = subj.Name.Substring(0, 80);
}
subj.Save();
System.Console.WriteLine(ft.dir + ' ' + ft.code + ' ' + ft.name);
ses.CommitChanges();
}
}
}
catch (Exception e) {
if (xlWorkSheet != null)
{
releaseObject(xlWorkSheet);
}
if (xlWorkBook != null)
{
releaseObject(xlWorkBook);
}
if (xlApp != null)
{
releaseObject(xlApp);
}
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;
GC.Collect();
System.Console.WriteLine(e);
throw e;
}
// Закрытие книги
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
if (args[0] == "order")
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
xlApp = new Excel.ApplicationClass();
// Открытие документа (книги) excel
xlWorkBook = xlApp.Workbooks.Open("e:\\order2.xlsx");
// Выбирается Лист (если существует)
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//
try {
while (true)
{
using (UnitOfWork ses = bses.BeginNestedUnitOfWork()) {
i++;
FinanceOrder fo = new FinanceOrder();
if (xlWorkSheet.get_Range("A" + i).Value2 == null)
{
break;
}
if (xlWorkSheet.get_Range("A" + i).Value2 is Double)
{
fo.buh_int_num = (Int32)((Double)xlWorkSheet.get_Range("A" + i).Value2);
}
if (xlWorkSheet.get_Range("B" + i).Value2 != null)
{
fo.buh_account = xlWorkSheet.get_Range("B" + i).Value2.ToString();
}
else
{
fo.buh_account = "";
}
fo.code = xlWorkSheet.get_Range("C" + i).Value2.ToString();
if (xlWorkSheet.get_Range("D" + i).Value2 != null)
{
fo.is_closed = true;
}
else
{
fo.is_closed = false;
}
if (xlWorkSheet.get_Range("E" + i).Value2 != null)
{
fo.subj = xlWorkSheet.get_Range("E" + i).Value2.ToString();
}
else
{
fo.subj = "";
}
if (xlWorkSheet.get_Range("F" + i).Value2 != null)
{
fo.name_short = xlWorkSheet.get_Range("F" + i).Value2.ToString();
}
else
{
fo.name_short = "";
}
if (xlWorkSheet.get_Range("G" + i).Value2 != null)
{
fo.name_full = xlWorkSheet.get_Range("G" + i).Value2.ToString();
}
else
{
fo.name_full = "";
}
if (xlWorkSheet.get_Range("H" + i).Value2 != null)
{
fo.desc = xlWorkSheet.get_Range("H" + i).Value2.ToString();
}
else
{
fo.desc = "";
}
if (xlWorkSheet.get_Range("I" + i).Value2 != null)
{
fo.data_from = xlWorkSheet.get_Range("I" + i).Value2.ToString();
}
else
{
fo.data_from = "";
}
if (xlWorkSheet.get_Range("J" + i).Value2 != null)
{
fo.data_to = xlWorkSheet.get_Range("J" + i).Value2.ToString();
}
else
{
fo.data_to = "";
}
if (xlWorkSheet.get_Range("K" + i).Value2 != null)
{
fo.base_doc = xlWorkSheet.get_Range("K" + i).Value2.ToString();
}
else
{
fo.base_doc = "";
}
if (xlWorkSheet.get_Range("L" + i).Value2 != null)
{
fo.work_type = xlWorkSheet.get_Range("L" + i).Value2.ToString();
}
else
{
fo.work_type = "";
}
if (xlWorkSheet.get_Range("M" + i).Value2 != null)
{
fo.finans = xlWorkSheet.get_Range("M" + i).Value2.ToString();
}
else
{
fo.finans = "";
}
if (xlWorkSheet.get_Range("N" + i).Value2 != null)
{
fo.source = xlWorkSheet.get_Range("N" + i).Value2.ToString();
}
else
{
fo.source = "";
}
if (xlWorkSheet.get_Range("O" + i).Value2 != null)
{
fo.army = xlWorkSheet.get_Range("O" + i).Value2.ToString();
}
else
{
fo.army = "";
}
if (xlWorkSheet.get_Range("P" + i).Value2 != null)
{
fo.nds_mode = xlWorkSheet.get_Range("P" + i).Value2.ToString();
}
else
{
fo.nds_mode = "";
}
if (xlWorkSheet.get_Range("Q" + i).Value2 != null)
{
fo.acc_mode = xlWorkSheet.get_Range("Q" + i).Value2.ToString();
}
else
{
fo.acc_mode = "";
}
if (xlWorkSheet.get_Range("R" + i).Value2 != null)
{
if (xlWorkSheet.get_Range("R" + i).Value2 is Double)
{
fo.koeff_ozm = (Decimal)((Double)xlWorkSheet.get_Range("R" + i).Value2);
}
}
if (xlWorkSheet.get_Range("S" + i).Value2 != null)
{
if (xlWorkSheet.get_Range("S" + i).Value2 is Double)
{
fo.koeff_kb = (Decimal)((Double)xlWorkSheet.get_Range("S" + i).Value2);
}
}
fmCSubjectExt subj = GetObject <fmCSubjectExt>(ses, fo.subj);
fmCOrderExt ord = GetObject <fmCOrderExt>(ses, fo.code);
if (ord == null)
{
ord = new fmCOrderExt(ses);
ord.Status = fmIOrderStatus.Loaded;
}
ord.Code = fo.code;
ord.Subject = subj;
ord.IsClosed = fo.is_closed;
ord.Name = fo.name_short;
ord.NameFull = fo.name_full;
ord.Description = fo.desc;
if (!String.IsNullOrEmpty(fo.data_from))
{
ord.DateBegin = new DateTime(Int32.Parse(fo.data_from.Substring(0, 4)),
Int32.Parse(fo.data_from.Substring(4, 2)),
Int32.Parse(fo.data_from.Substring(6, 2)));
}
if (!String.IsNullOrEmpty(fo.data_to))
{
ord.DateEnd = new DateTime(Int32.Parse(fo.data_to.Substring(0, 4)),
Int32.Parse(fo.data_to.Substring(4, 2)),
Int32.Parse(fo.data_to.Substring(6, 2)));
}
if (!String.IsNullOrEmpty(fo.acc_mode))
{
ord.AnalitycAccouterType = GetObject <fmСOrderAnalitycAccouterType>(ses, fo.acc_mode);
}
if (!String.IsNullOrEmpty(fo.nds_mode))
{
ord.AnalitycAVT = GetObject <fmСOrderAnalitycAVT>(ses, fo.nds_mode);
}
ord.BuhAccount = fo.buh_account;
ord.BuhIntNum = fo.buh_int_num;
ord.KoeffKB = fo.koeff_kb;
ord.KoeffOZM = fo.koeff_ozm;
ord.SourceOther = fo.base_doc;
// ord.
if (!String.IsNullOrEmpty(fo.work_type))
{
ord.AnalitycWorkType = GetObject <fmСOrderAnalitycWorkType>(ses, fo.work_type);
}
if (!String.IsNullOrEmpty(fo.acc_mode))
{
ord.AnalitycFinanceSource = GetObject <fmСOrderAnalitycFinanceSource>(ses, fo.finans);
}
if (!String.IsNullOrEmpty(fo.acc_mode))
{
ord.AnalitycOrderSource = GetObject <fmСOrderAnalitycOrderSource>(ses, fo.source);
}
if (!String.IsNullOrEmpty(fo.acc_mode))
{
ord.AnalitycMilitary = GetObject <fmСOrderAnalitycMilitary>(ses, fo.army);
}
System.Console.WriteLine(fo.subj + ' ' + fo.code + ' ' + fo.name_short);
ses.CommitChanges();
}
}
}
catch (Exception e) {
if (xlWorkSheet != null)
{
releaseObject(xlWorkSheet);
}
if (xlWorkBook != null)
{
releaseObject(xlWorkBook);
}
if (xlApp != null)
{
releaseObject(xlApp);
}
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;
GC.Collect();
System.Console.WriteLine(e);
throw e;
}
// Закрытие книги
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
}
static public void ImportInvoices(IObjectSpace os, fmCAVTInvoiceRegister register, String file_name)
{
// OpenFileDialog dialog = new OpenFileDialog();
// if (dialog.ShowDialog() == DialogResult.OK) {
FixedFileEngine engine = new FixedFileEngine(typeof(fmCAVTInvoiceImport));
fmCAVTInvoiceImport[] imp_res = (fmCAVTInvoiceImport[])engine.ReadFile(file_name);
IList <fmCAVTInvoiceType> inv_types = os.GetObjects <fmCAVTInvoiceType>();
IList <fmCAVTInvoiceTransferType> inv_transfer_types = os.GetObjects <fmCAVTInvoiceTransferType>();
IList <fmCAVTInvoiceOperationType> inv_oper_types = os.GetObjects <fmCAVTInvoiceOperationType>();
Int32 count = 0;
UInt32 seq_num = 0;
foreach (fmCAVTInvoiceImport imp_rec in imp_res)
{
imp_rec.SF_VO_CODE = imp_rec.SF_VO_CODE.Trim();
imp_rec.SF_INT_NUMBER = imp_rec.SF_INT_NUMBER.Trim();
imp_rec.SF_NUMBER = imp_rec.SF_NUMBER.Trim();
Decimal summ_cost = Decimal.Parse(imp_rec.SUMM_COST.Trim().Replace('.', ','));
Decimal summ_nds = Decimal.Parse(imp_rec.SUMM_NDS.Trim().Replace('.', ','));
Decimal summ_sub_cost = Decimal.Parse(imp_rec.SUMM_SUB_COST.Trim().Replace('.', ','));
Decimal summ_sub_nds = Decimal.Parse(imp_rec.SUMM_SUB_NDS.Trim().Replace('.', ','));
DateTime sf_date = default(DateTime);
DateTime.TryParseExact(imp_rec.SF_DATE.Trim(), "yyyyMMdd", null, System.Globalization.DateTimeStyles.None, out sf_date);
fmCAVTInvoiceBase invoice = os.FindObject <fmCAVTInvoiceBase>(
XPQuery <fmCAVTInvoiceBase> .TransformExpression(
((ObjectSpace)os).Session,
rec => rec.Supplier.Code == imp_rec.SF_VO_CODE &&
rec.RegNumber == imp_rec.SF_INT_NUMBER &&
rec.Date >= sf_date &&
rec.Date < sf_date.AddDays(1)
));
//if (invoice == null) {
// count++;
// System.Console.WriteLine(imp_rec.SF_INT_NUMBER + " " + imp_rec.SF_NUMBER + " " + imp_rec.SF_DATE + " " + summ_cost + " " + summ_nds);
//}
if (invoice == null)
{
crmCParty party = os.GetObjects <crmCParty>(new BinaryOperator("Code", imp_rec.SF_VO_CODE)).FirstOrDefault();
invoice = os.CreateObject <fmCAVTInvoiceBase>();
invoice.RegNumber = imp_rec.SF_INT_NUMBER;
invoice.Number = imp_rec.SF_NUMBER;
invoice.Date = sf_date;
invoice.Supplier = party;
invoice.Customer = register.Party;
invoice.SummAVT = Decimal.Parse(imp_rec.SUMM_NDS.Trim().Replace('.', ','));
invoice.SummCost = Decimal.Parse(imp_rec.SUMM_COST.Trim().Replace('.', ','));
}
else
{
invoice.SummAVT = Decimal.Parse(imp_rec.SUMM_NDS.Trim().Replace('.', ','));
invoice.SummCost = Decimal.Parse(imp_rec.SUMM_COST.Trim().Replace('.', ','));
fmCAVTInvoiceRegisterLine line_check = os.FindObject <fmCAVTInvoiceRegisterLine>(
CriteriaOperator.And(new BinaryOperator("InvoiceVersion", invoice.Current)), true);
if (line_check != null)
{
continue;
}
}
fmCAVTInvoiceRegisterLine line = register.InLines.Where(rec => rec.Invoice == invoice).FirstOrDefault();
if (line == null)
{
line = os.CreateObject <fmCAVTInvoiceRegisterLine>();
register.InLines.Add(line);
}
line.SequenceNumber = seq_num++;
// line_doc.DateTransfer = invoice.Date;
line.Invoice = invoice;
line.TransferType = inv_transfer_types.Where(rec => rec.Code == "1").FirstOrDefault();
if (String.IsNullOrEmpty(imp_rec.SF_TRANS_DATE.Trim()))
{
DateTime trans_date = default(DateTime);
DateTime.TryParseExact(imp_rec.SF_TRANS_DATE.Trim(), "yyyyMMdd", null, System.Globalization.DateTimeStyles.None, out trans_date);
line.DateTransfer = trans_date;
}
if (line.DateTransfer < sf_date)
{
line.DateTransfer = sf_date;
}
line.OperationType = inv_oper_types.Where(rec => rec.Code == imp_rec.SF_OPER_TYPE.Trim() ||
rec.Code == '0' + imp_rec.SF_OPER_TYPE.Trim()).FirstOrDefault();
}
System.Console.WriteLine("All " + count);
}
static public void ImportBookBayMain2014(IObjectSpace os, fmCAVTBookVAT book)
{
Int32 kvartal = Int32.Parse(book.PeriodKV);
String period_m1 = book.PeriodYYYY + ((kvartal - 1) * 3 + 1).ToString("00");
String period_m2 = book.PeriodYYYY + ((kvartal - 1) * 3 + 2).ToString("00");
String period_m3 = book.PeriodYYYY + ((kvartal - 1) * 3 + 3).ToString("00");
IList <fmCAVTInvoiceOperationType> oper_types = os.GetObjects <fmCAVTInvoiceOperationType>();
os.Delete(book.BookVATRecords);
UInt32 seq_num = 1;
IList <fmCAVTBookBuhRecord> buhrecs = os.GetObjects <fmCAVTBookBuhRecord>(
XPQuery <fmCAVTBookBuhRecord> .TransformExpression(((ObjectSpace)os).Session,
rec => rec.BookBuhImport.IsNotUse == false && rec.BookType == "B" && (
rec.PeriodOtchet == period_m1 ||
rec.PeriodOtchet == period_m2 ||
rec.PeriodOtchet == period_m3
//&& (rec.RecordType != "AON" ||
// rec.RecordType == "AON" &&
// rec.RecordSummType == "NDS" &&
// rec.SummVAT != 0)
)
));
var invoice_buhrecs = buhrecs.GroupBy(rec => new {
// rec.RecordType,
rec.AVTInvoicePartyCode,
rec.AVTInvoiceType,
rec.AVTInvoiceRegNumber,
rec.AVTInvoiceNumber,
rec.AVTInvoiceDate
}).OrderBy(key => key.Key.AVTInvoicePartyCode);
//
foreach (var invoice_buhrec in invoice_buhrecs)
{
if (String.IsNullOrEmpty(invoice_buhrec.Key.AVTInvoiceType.Trim()))
{
continue;
}
if (invoice_buhrec.Key.AVTInvoiceType.Trim() == "рд")
{
continue;
}
fmCAVTBookVATRecord rec = os.CreateObject <fmCAVTBookVATRecord>();
crmCParty party = os.GetObjects <crmCParty>(new BinaryOperator("Code", invoice_buhrec.Key.AVTInvoicePartyCode)).FirstOrDefault();
rec.BuhRecordType =
invoice_buhrec.Select(x => x.RecordType).Distinct().OrderBy(x => x).Aggregate((x, y) => x + ";" + y);
rec.Party = party;
rec.VATInvoiceType = invoice_buhrec.Key.AVTInvoiceType;
rec.VATInvoiceRegNumber = invoice_buhrec.Key.AVTInvoiceRegNumber;
rec.VATInvoiceNumber = invoice_buhrec.Key.AVTInvoiceNumber;
rec.VATInvoiceDate = invoice_buhrec.Key.AVTInvoiceDate;
rec.Invoice = os.FindObject <fmCAVTInvoiceBase>(
CriteriaOperator.And(new BinaryOperator("Number", invoice_buhrec.Key.AVTInvoiceNumber),
new BinaryOperator("Date", invoice_buhrec.Key.AVTInvoiceDate.Date, BinaryOperatorType.GreaterOrEqual),
new BinaryOperator("Date", invoice_buhrec.Key.AVTInvoiceDate.Date.AddDays(1), BinaryOperatorType.Less),
new BinaryOperator("Supplier", party)));
if (invoice_buhrec.Key.AVTInvoiceType == "ятю" && rec.Invoice == null)
{
rec.Invoice = os.FindObject <fmCAVTInvoiceBase>(
CriteriaOperator.And(new BinaryOperator("Number", invoice_buhrec.Key.AVTInvoiceNumber),
new BinaryOperator("Date", invoice_buhrec.Key.AVTInvoiceDate.Date, BinaryOperatorType.GreaterOrEqual),
new BinaryOperator("Date", invoice_buhrec.Key.AVTInvoiceDate.Date.AddDays(1), BinaryOperatorType.Less),
new BinaryOperator("Customer", party)));
}
rec.PayDate = invoice_buhrec.Select(buhrec => buhrec.PayDocDate).Max();
rec.BuhDate = invoice_buhrec.Select(buhrec => buhrec.BuhDocDate).Min();
//Decimal SummNoVAT_18;
//Decimal SummNoVAT_10;
//rec.SummVAT_18 = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "2").Sum(buhrec => buhrec.SummVAT);
//SummNoVAT_18 = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "2").Sum(buhrec => buhrec.SummVATCost);
//rec.SummVAT_10 = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "3").Sum(buhrec => buhrec.SummVAT);
//SummNoVAT_10 = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "3").Sum(buhrec => buhrec.SummVATCost);
//rec.SummCost_NoVAT = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "5").Sum(buhrec => buhrec.SummAll);
//rec.SummCost_18 = Decimal.Round(rec.SummVAT_18 * 100m / 18m, 2);
//rec.SummCost_10 = Decimal.Round(rec.SummVAT_10 * 100m / 10m, 2);
//rec.SummCost_NoVAT = rec.SummCost_NoVAT +
// Decimal.Round(SummNoVAT_18 * 118m / 18m, 2) +
// Decimal.Round(SummNoVAT_10 * 110m / 10m, 2);
rec.SummBayCost = invoice_buhrec.Sum(buhrec => buhrec.SummCost);
rec.SummBayVatCharge = invoice_buhrec.Sum(buhrec => buhrec.SummVATIn);
rec.SummBayVatDeduction = invoice_buhrec.Sum(buhrec => buhrec.SummVAT);
rec.SummBayVatInCost = invoice_buhrec.Sum(buhrec => buhrec.SummVATCost);
rec.SummBayVatExp = invoice_buhrec.Sum(buhrec => buhrec.SummVATExp);
rec.SummBayVatOtherCredit = invoice_buhrec.Sum(buhrec => buhrec.SummVATCrdOther);
rec.SummBayVatOtherCredit += invoice_buhrec.Sum(buhrec => buhrec.SummVATNoInvoice);
if (rec.SummBayVatDeduction == 0)
{
os.Delete(rec);
continue;
}
if (rec.SummBayCost == 0)
{
rec.SummBayCost = Decimal.Round((rec.SummBayVatDeduction + rec.SummBayVatInCost) * 100 / 18, 2);
}
rec.SequenceNumber = seq_num++;
switch (rec.BuhRecordType)
{
case "PAY":
rec.OperationType = oper_types.FirstOrDefault(x => x.Code == "01");
break;
case "BAY":
rec.OperationType = oper_types.FirstOrDefault(x => x.Code == "01");
break;
case "AIN":
rec.OperationType = oper_types.FirstOrDefault(x => x.Code == "02");
break;
case "AON":
rec.OperationType = oper_types.FirstOrDefault(x => x.Code == "02");
break;
case "EAT":
rec.OperationType = oper_types.FirstOrDefault(x => x.Code == "01");
break;
case "EXP":
rec.OperationType = oper_types.FirstOrDefault(x => x.Code == "01");
break;
case "SMN":
rec.OperationType = oper_types.FirstOrDefault(x => x.Code == "01");
break;
case "SPC":
rec.OperationType = oper_types.FirstOrDefault(x => x.Code == "01");
break;
default:
rec.OperationType = oper_types.FirstOrDefault(x => x.Code == "01");
break;
}
book.BookVATRecords.Add(rec);
}
IList <fmCAVTBookBuhStruct> book_structs = os.GetObjects <fmCAVTBookBuhStruct>();
foreach (var book_struct in book_structs)
{
foreach (var record in book_struct.InInvoiceRecords)
{
if (book.DatePeriodStart.Date <= record.BayDate.Date && record.BayDate.Date <= book.DatePeriodStop.Date)
{
if (record.BaySummAll == 0 || record.BaySummVAT == 0 && record.BayVATRate.Code != "0%")
{
continue;
}
fmCAVTBookVATRecord book_rec = os.CreateObject <fmCAVTBookVATRecord>();
// rec.BuhRecordType = invoice_buhrec.Key.RecordType;
book_rec.BookBuhStruct = book_struct;
book_rec.OperationType = record.OperationType;
if (record.InvoiceType == "ятю")
{
book_rec.BuhRecordType = "AIN";
}
else
{
book_rec.BuhRecordType = "BAY";
}
book_rec.Party = record.PartnerParty;
book_rec.VATInvoiceRegNumber = record.InvoiceRegNumber;
book_rec.VATInvoiceType = record.InvoiceType;
book_rec.VATInvoiceNumber = record.InvoiceNumber;
book_rec.VATInvoiceDate = record.InvoiceDate;
book_rec.Invoice = record.Invoice;
if (record.Invoice != null)
{
book_rec.InvoiceVersion = record.Invoice.Current;
}
book_rec.PayDate = record.SaleDate;
book_rec.BuhDate = record.SaleDate;
book_rec.SummBayVatCharge = record.BaySummVAT;
book_rec.SummBayVatDeduction = Decimal.Round(book_rec.SummBayVatCharge * book_struct.BayNorma, 2);
book_rec.SummBayVatInCost = book_rec.SummBayVatCharge - book_rec.SummBayVatDeduction;
book_rec.SummBayCost = record.BaySummAll - record.BaySummVAT;
// if (record.InvoiceType == "ятг") book_rec.PayDate = book_rec.BuhDate;
// if (record.Nds)
// book_rec.SummVAT_18 = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "2").Sum(buhrec => buhrec.SummVAT);
book_rec.SequenceNumber = seq_num++;
book.BookVATRecords.Add(book_rec);
}
}
}
ReNumber(os, book);
}
static public void ImportBookBayMain(IObjectSpace os, fmCAVTBookVAT book)
{
Int32 kvartal = Int32.Parse(book.PeriodKV);
String period_m1 = book.PeriodYYYY + ((kvartal - 1) * 3 + 1).ToString("00");
String period_m2 = book.PeriodYYYY + ((kvartal - 1) * 3 + 2).ToString("00");
String period_m3 = book.PeriodYYYY + ((kvartal - 1) * 3 + 3).ToString("00");
os.Delete(book.BookVATRecords);
IList <fmCAVTBookBuhRecord> buhrecs = os.GetObjects <fmCAVTBookBuhRecord>(
XPQuery <fmCAVTBookBuhRecord> .TransformExpression(((ObjectSpace)os).Session,
rec => rec.BookType == "B" && (
rec.PeriodOtchet == period_m1 ||
rec.PeriodOtchet == period_m2 ||
rec.PeriodOtchet == period_m3
//&& (rec.RecordType != "AON" ||
// rec.RecordType == "AON" &&
// rec.RecordSummType == "NDS" &&
// rec.SummVAT != 0)
)
));
var invoice_buhrecs = buhrecs.GroupBy(rec => new {
rec.RecordType,
rec.AVTInvoicePartyCode,
rec.AVTInvoiceType,
rec.AVTInvoiceRegNumber,
rec.AVTInvoiceNumber,
rec.AVTInvoiceDate
}).OrderBy(key => key.Key.AVTInvoicePartyCode);
//
UInt32 seq_num = 1;
foreach (var invoice_buhrec in invoice_buhrecs)
{
if (String.IsNullOrEmpty(invoice_buhrec.Key.AVTInvoiceType.Trim()))
{
continue;
}
if (invoice_buhrec.Key.AVTInvoiceType.Trim() == "рд")
{
continue;
}
fmCAVTBookVATRecord rec = os.CreateObject <fmCAVTBookVATRecord>();
crmCParty party = os.GetObjects <crmCParty>(new BinaryOperator("Code", invoice_buhrec.Key.AVTInvoicePartyCode)).FirstOrDefault();
rec.BuhRecordType = invoice_buhrec.Key.RecordType;
rec.Party = party;
rec.VATInvoiceType = invoice_buhrec.Key.AVTInvoiceType;
rec.VATInvoiceRegNumber = invoice_buhrec.Key.AVTInvoiceRegNumber;
rec.VATInvoiceNumber = invoice_buhrec.Key.AVTInvoiceNumber;
rec.VATInvoiceDate = invoice_buhrec.Key.AVTInvoiceDate;
rec.Invoice = os.FindObject <fmCAVTInvoiceBase>(
CriteriaOperator.And(new BinaryOperator("Number", invoice_buhrec.Key.AVTInvoiceNumber),
new BinaryOperator("Date", invoice_buhrec.Key.AVTInvoiceDate.Date, BinaryOperatorType.GreaterOrEqual),
new BinaryOperator("Date", invoice_buhrec.Key.AVTInvoiceDate.Date.AddDays(1), BinaryOperatorType.Less),
new BinaryOperator("Supplier", party)));
rec.PayDate = invoice_buhrec.Select(buhrec => buhrec.PayDocDate).Max();
rec.BuhDate = invoice_buhrec.Select(buhrec => buhrec.BuhDocDate).Min();
Decimal SummNoVAT_18;
Decimal SummNoVAT_10;
rec.SummVAT_18 = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "2").Sum(buhrec => buhrec.SummVAT);
SummNoVAT_18 = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "2").Sum(buhrec => buhrec.SummVATCost);
rec.SummVAT_10 = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "3").Sum(buhrec => buhrec.SummVAT);
SummNoVAT_10 = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "3").Sum(buhrec => buhrec.SummVATCost);
rec.SummCost_NoVAT = invoice_buhrec.Where(buhrec => buhrec.NDSRate == "5").Sum(buhrec => buhrec.SummAll);
rec.SummCost_18 = Decimal.Round(rec.SummVAT_18 * 100m / 18m, 2);
rec.SummCost_10 = Decimal.Round(rec.SummVAT_10 * 100m / 10m, 2);
rec.SummCost_NoVAT = rec.SummCost_NoVAT +
Decimal.Round(SummNoVAT_18 * 118m / 18m, 2) +
Decimal.Round(SummNoVAT_10 * 110m / 10m, 2);
if (rec.SummAll == 0 && rec.SummVAT_18 == 0 && rec.SummVAT_10 == 0)
{
os.Delete(rec);
continue;
}
rec.SequenceNumber = seq_num++;
book.BookVATRecords.Add(rec);
switch (invoice_buhrec.Key.RecordType)
{
case "PAY":
break;
case "AIN":
break;
case "AON":
break;
case "EAT":
break;
case "EXP":
break;
case "SMN":
break;
case "SPC":
break;
default:
break;
}
}
}
protected TrwPartyParty GetTrwParty(IObjectSpace os, IList <TrwPartyParty> trw_partys, crmCParty party)
{
if (party.Person == null)
{
return(null);
}
TrwPartyParty trw_party = trw_partys.FirstOrDefault(x => x.Person == party.Person);
if (trw_party == null)
{
trw_party = TrwPartyParty.LocateTrwParty(os, party);
trw_partys.Add(trw_party);
}
return(trw_party);
}
public void AutoBindingResearch()
{
// Задача исследования.
// 1. Подсчёт количества загруженных документов в систему за определённый период (за 18-19 апреля, например).
// 2. Подсчёт количества заявок в системе со статусом IN_PAY, да и вообще сколько заявок с какими статусами.
// 3. Подсчёт актального количества связей платёжных документов и заявок.
// 4. Подсчёт количества потенциально возможных связей платёжных документов и заявок.
// 5. Если обнаружились непривязанные, но могшие быть привязанными автоматически заявками, выяснить, почему они не привязались.
//Decimal _Accuracy = 0.01m; // Сопоставление с точностью до копейки
DateTime startDate = new DateTime(2012, 4, 1);
DateTime endDate = new DateTime(2012, 4, 20);
using (UnitOfWork uow = new UnitOfWork(Common.dataLayer)) {
// Проверка уже привязавшихся заявок и документов
XPQuery <fmCPRRepaymentJurnal> xpqBinding = new XPQuery <fmCPRRepaymentJurnal>(uow);
// 3. Подсчёт актуального количества связей платёжных документов и заявок.
Int32 BindingCount = (from record in xpqBinding
//where record.State == PaymentRequestStates.IN_PAYMENT
select new {
record.PaymentDocument,
record.PaymentRequest
}).Distinct().Count();
//var DocReqLink = from record in xpqBinding
// select new {
// record.PaymentDocument,
// record.PaymentRequest
// };
//foreach (var record in DocReqLink) {
// //Assert.AreNotEqual(record.PaymentDocument.PaymentPayerRequisites.BankAccount, record.PaymentDocument.PaymentReceiverRequisites.BankAccount);
// if (record.PaymentRequest.Valuta != null || record.PaymentRequest.PaymentValuta != null) {
// Assert.AreEqual(record.PaymentRequest.Valuta, record.PaymentRequest.PaymentValuta);
// if (record.PaymentRequest.Valuta != null) {
// Assert.AreEqual(record.PaymentRequest.Valuta.Code, "RUB");
// }
// if (record.PaymentRequest.PaymentValuta != null) {
// Assert.AreEqual(record.PaymentRequest.PaymentValuta.Code, "RUB");
// }
// }
//}
// 1. Подсчёт количества загруженных документов в систему за определённый период (за 18-19 апреля, например).
XPQuery <fmCDocRCB> paymentDocuments = new XPQuery <fmCDocRCB>(uow);
var queryPaymentDocuments = from paymentDocument in paymentDocuments
where paymentDocument.DocDate.Date >= startDate &&
paymentDocument.DocDate.Date < endDate
//&& paymentDocument.
select paymentDocument;
Int32 countPaymentDocumentcount = queryPaymentDocuments.Count();
// 2. Подсчёт количества заявок в системе со статусом IN_PAY, да и вообще сколько заявок с какими статусами.
XPQuery <fmCPRPaymentRequest> paymentRequests = new XPQuery <fmCPRPaymentRequest>(uow);
var queryPaymentRequestByStatus = from paymentRequest in paymentRequests
group paymentRequest by new {
paymentRequest.State,
paymentRequest.Valuta
}
into gPR
select new {
State = gPR.Key.State,
Valuta = gPR.Key.Valuta,
Count = gPR.Count()
};
// 4. Проверка состоит в том, чтобы поискать для каждого оставшегося непривязанным документа заявку по установленным правилам (статус и плательщик с получателем)
// Поиск подходящей заявки (Статус, Плательщик, Получатель, остаточные суммы)
crmCParty OurParty = GetOurPartyByCode(uow, "2518");
Int32 FreeRequestCount = 0;
Int32 CountAll = 0, CountNoBind = 0, CountPotenAllBind = 0, CountPotenNoBind = 0;
foreach (var paymentDocument in queryPaymentDocuments)
{
Int32 PaymentDocumentBusyCount = (from record in xpqBinding
where record.PaymentDocument == paymentDocument
select record).Count();
CountAll++;
if (PaymentDocumentBusyCount == 0)
{
CountNoBind++;
}
else
{
CountNoBind = CountNoBind;
}
//if (PaymentDocumentBusyCount == 0) { // т.е. документ не имеет никаких привязок (его остатоная сумма равна его начальной сумме)
//crmBankAccount bankAccount = null;
//if (paymentDocument.PaymentPayerRequisites.Party == OurParty) {
// bankAccount = paymentDocument.PaymentPayerRequisites.BankAccount;
//} else {
// bankAccount = paymentDocument.PaymentReceiverRequisites.BankAccount;
//}
//// Валюта платежа
//csValuta paymentValuta = crmBankAccount.GetValutaByBankAccount(uow, bankAccount);
// Поиск подходящей заявки
//XPQuery<fmCPRPaymentRequest> searchedPymentRequests = new XPQuery<fmCPRPaymentRequest>(uow);
var querySearchedPaymentRequests = (from pr in paymentRequests
where //pr.State == PaymentRequestStates.IN_PAYMENT
//&&
pr.PartyPayReceiver.INN == paymentDocument.PaymentReceiverRequisites.INN &&
pr.PartyPaySender.INN == paymentDocument.PaymentPayerRequisites.INN
select pr).ToList <fmCPRPaymentRequest>();
Debug.WriteLine(String.Format("{0} {1} {2} {3}", CountAll, querySearchedPaymentRequests.Count, paymentDocument.DocNumber, paymentDocument.DocDate));
foreach (var elem in querySearchedPaymentRequests)
{
// Отбраковка:
// (1) сумма заявки не должна быть исчерпана полностью,
// (2) остаточная сумма должна равняться величине непокрытия Платёжного документа
Decimal operationPaymentDocSumIn = 0, operationPaymentDocSumOut = 0;
GetPaymentDocSumByOperationJournal(uow, paymentDocument, out operationPaymentDocSumIn, out operationPaymentDocSumOut);
if (elem.Summ == operationPaymentDocSumIn || elem.Summ == operationPaymentDocSumOut)
{
FreeRequestCount++;
Debug.WriteLine(String.Format("Ok {0} Date {1}", paymentDocument.DocNumber, paymentDocument.DocDate));
CountPotenAllBind++;
if (PaymentDocumentBusyCount == 0)
{
CountPotenNoBind++;
}
}
/*
* Decimal paymentRequestSumIn = 0, paymentRequestSumOut = 0; // Эти суммы в валюте платежа
* Decimal paymentRequestSumObligationIn = 0, paymentRequestSumObligationOut = 0; // Эти суммы в валюте обязательств
* GetPaymentRequestSumByRepaymentJournal(
* uow,
* elem,
* out paymentRequestSumIn,
* out paymentRequestSumOut,
* out paymentRequestSumObligationIn,
* out paymentRequestSumObligationOut
* );
*
* //Decimal deltaRequestSum = GetRequestSumByCourse(uow, paymentDocument, elem, elem.Valuta); // В валюте платежа
* Decimal deltaRequestSum = GetRequestSumByCourse(uow, paymentDocument, elem, elem.Valuta); // В валюте платежа
* //if (paymentRequest.PartyPayReceiver.INN == OurParty.INN) {
* if (elem.PartyPayReceiver == OurParty) {
* deltaRequestSum -= paymentRequestSumIn;
* } else {
* deltaRequestSum -= paymentRequestSumOut;
* }
* if (Decimal.Compare(Math.Abs(deltaRequestSum), _Accuracy) <= 0)
* continue; // Переход к следующей заявке (тогда у заявки должен был бы быть статус PAYED и она не должна была попасть в рассмотрение - это предусловие контракта)
*
* if (GetAnswer(uow, paymentDocument, elem)) {
* FreeRequestCount += 1;
* }
*/
}
//}
}
//uow.CommitChanges();
}
}
protected virtual crmUserParty Prepare_crmUserParty(Session ssn, string modificator, crmCParty partyRu)
{
crmUserParty userParty1 = new crmUserParty(ssn);
userParty1.Party = partyRu;
return(userParty1);
}
static public void ImportInvoices(IObjectSpace os, String file_name)
{
// OpenFileDialog dialog = new OpenFileDialog();
// if (dialog.ShowDialog() == DialogResult.OK) {
FixedFileEngine engine = new FixedFileEngine(typeof(InvoiceImport));
InvoiceImport[] imp_res = (InvoiceImport[])engine.ReadFile(file_name);
IList <fmCAVTInvoiceType> inv_types = os.GetObjects <fmCAVTInvoiceType>();
IList <fmCAVTInvoiceTransferType> inv_transfer_types = os.GetObjects <fmCAVTInvoiceTransferType>();
IList <fmCAVTInvoiceOperationType> inv_oper_types = os.GetObjects <fmCAVTInvoiceOperationType>();
Int32 count = 0;
fmCAVTInvoiceType sf_sfz_type = os.GetObjects <fmCAVTInvoiceType>().First(x => x.Prefix == "Z");
foreach (InvoiceImport imp_rec in imp_res)
{
imp_rec.SF_VO_CODE = imp_rec.SF_VO_CODE.Trim();
imp_rec.SF_INT_NUMBER = imp_rec.SF_INT_NUMBER.Trim();
imp_rec.SF_NUMBER = imp_rec.SF_NUMBER.Trim();
Decimal summ_cost = Decimal.Parse(imp_rec.SUMM_COST.Trim().Replace('.', ','));
Decimal summ_nds = Decimal.Parse(imp_rec.SUMM_NDS.Trim().Replace('.', ','));
Decimal summ_sub_cost = Decimal.Parse(imp_rec.SUMM_SUB_COST.Trim().Replace('.', ','));
Decimal summ_sub_nds = Decimal.Parse(imp_rec.SUMM_SUB_NDS.Trim().Replace('.', ','));
DateTime sf_date = default(DateTime);
DateTime.TryParseExact(imp_rec.SF_DATE.Trim(), "yyyyMMdd", null, System.Globalization.DateTimeStyles.None, out sf_date);
crmCParty party = os.GetObjects <crmCParty>(new BinaryOperator("Code", imp_rec.SF_VO_CODE)).FirstOrDefault();
if (party == null)
{
System.Console.WriteLine("SF " + imp_rec.SF_NUMBER + " party not found (" + imp_rec.SF_VO_CODE + ")");
continue;
}
Основание.ТипИсточника ts;
if (imp_rec.SF_IO_TYPE == "I")
{
ts = Основание.ТипИсточника.ВХОДЯЩИЙ;
}
else if (imp_rec.SF_IO_TYPE == "O")
{
ts = Основание.ТипИсточника.ИСХОДЯЩИЙ;
}
else
{
throw new ArgumentOutOfRangeException("SF " + imp_rec.SF_NUMBER + " неопределен тип входящий/исходящий");
}
Основание.ТипОснования tsf;
switch (imp_rec.SF_TYPE)
{
case "СЧФ":
tsf = Основание.ТипОснования.СЧФ;
break;
case "УПД":
tsf = Основание.ТипОснования.УПД;
break;
case "СФА":
tsf = Основание.ТипОснования.СФА;
break;
case "СФЗ":
tsf = Основание.ТипОснования.СФЗ;
break;
case "СЧГ":
tsf = Основание.ТипОснования.СЧГ;
break;
case "БЖД":
tsf = Основание.ТипОснования.БЖД;
break;
case "СФВ":
tsf = Основание.ТипОснования.СФВ;
break;
case "БСО":
tsf = Основание.ТипОснования.БСО;
break;
case "ЧЕК":
tsf = Основание.ТипОснования.ЧЕК;
break;
default:
System.Console.WriteLine("SF: " + imp_rec.SF_NUMBER + " странный тип (" + imp_rec.SF_TYPE + ")");
continue;
}
String inn = "";
String kpp = "";
ЛицоТип party_type = ЛицоТип.НЕЗАДАН;
if (party.Person != null)
{
if (party.Person.Address.Country.CodeAlfa2 == "RU")
{
// Type party.ComponentTypeComponentObject.GetType();
if (party.ComponentType == typeof(crmCLegalPerson) ||
party.ComponentType == typeof(crmCLegalPersonUnit))
{
party_type = ЛицоТип.ЮР_ЛИЦО;
inn = party.INN;
if (inn.Length == 9)
{
inn = "0" + inn;
}
kpp = party.KPP;
if (inn.Length == 8)
{
kpp = "0" + kpp;
}
if (inn.Length != 10)
{
System.Console.WriteLine("Party: " + party.Code + " fail INN (" + inn + ")");
continue;
}
if (kpp.Length != 9)
{
System.Console.WriteLine("Party: " + party.Code + " fail KPP (" + kpp + ")");
continue;
}
}
else
{
if (party.ComponentType == typeof(crmCBusinessman))
{
party_type = ЛицоТип.ПРЕДПРИНИМАТЕЛЬ;
inn = party.INN;
if (inn.Length == 11)
{
inn = "0" + inn;
}
if (inn.Length != 12)
{
System.Console.WriteLine("Party: " + party.Code + " fail INN (" + inn + ")");
continue;
}
}
else
if (party.ComponentType == typeof(crmCPhysicalParty))
{
party_type = ЛицоТип.ФИЗ_ЛИЦО;
}
}
}
else
{
party_type = ЛицоТип.ИНО_ПАРТНЕР;
System.Console.WriteLine("Party: " + party.Code + " инопартнер ");
}
}
if (party.Code == "2706")
{
party_type = ЛицоТип.ОЗНИЦА;
}
//
String sale_inn = "5012039795";
if (ts == Основание.ТипИсточника.ВХОДЯЩИЙ)
{
sale_inn = inn;
}
// !!!!!!!!!!!!!!!!!!!!!!!!!!
// Выбраковываем СФ
if (String.IsNullOrEmpty(sale_inn) || String.IsNullOrEmpty(imp_rec.SF_REGNUM.Trim()) && tsf != Основание.ТипОснования.СФЗ)
{
continue;
}
// !!!!!!!!!!!!!!!!!!!!!!!!!!
Основание sf = os.FindObject <Основание>(
XPQuery <Основание> .TransformExpression(
((ObjectSpace)os).Session,
rec =>
rec.ИннПродавца == sale_inn &&
rec.Номер == imp_rec.SF_NUMBER &&
rec.Дата >= sf_date &&
rec.Дата < sf_date.AddDays(1)
));
if (sf == null)
{
sf = os.CreateObject <Основание>();
sf.Источник = ts;
sf.ИНН = inn;
sf.Номер = imp_rec.SF_NUMBER;
sf.Дата = sf_date;
sf.КПП = kpp;
}
sf.Корректировка = Основание.ТипПодчиненности.ОСНОВНОЙ;
sf.Источник = ts;
sf.Тип = tsf;
sf.ЛицоТип = party_type;
ОснованиеДокумент sfdoc = null;
String sfdoc_sver = imp_rec.SF_PRAV_NUMBER.Trim();
if (String.IsNullOrEmpty(sfdoc_sver))
{
sfdoc_sver = "0";
}
UInt16 sfdoc_ver = 0;
UInt16.TryParse(sfdoc_sver, out sfdoc_ver);
DateTime sfdoc_date = default(DateTime);
DateTime.TryParseExact(imp_rec.SF_DATE.Trim(), "yyyyMMdd", null, System.Globalization.DateTimeStyles.None, out sfdoc_date);
foreach (ОснованиеДокумент doc in sf.Документы)
{
if (doc.НомерИсправления == sfdoc_ver)
{
sfdoc = doc;
break;
}
}
if (sfdoc == null)
{
sfdoc = os.CreateObject <ОснованиеДокумент>();
sf.Документы.Add(sfdoc);
sfdoc.НомерИсправления = sfdoc_ver;
if (sf.ДействующийДокумент.НомерИсправления < sfdoc.НомерИсправления)
{
sf.ДействующийДокумент = sfdoc;
}
}
sfdoc.ДатаИсправления = sfdoc_date;
sfdoc.егНомер = imp_rec.SF_REGNUM.Trim();
if (sf.Тип == Основание.ТипОснования.СФЗ && String.IsNullOrEmpty(sfdoc.егНомер))
{
Int32 IntNumber = fmCAVTInvoiceNumberGenerator.GenerateNumber(((ObjectSpace)os).Session, sf.ДействующийДокумент.CID, sf_sfz_type, sf.Дата, 0);
sfdoc.егНомер = sf_sfz_type.Prefix + sf.Дата.ToString("yyyyMM").Substring(2, 4) + IntNumber.ToString("00000");
}
sfdoc.КодПартнера = party.Code;
sfdoc.НаименКонтрагента = party.Name;
sfdoc.СуммаВсего = summ_cost + summ_nds;
sfdoc.СуммаНДС = summ_nds;
sfdoc.СуммаВсегоУвел = sfdoc.СуммаВсего + summ_sub_cost;
sfdoc.СуммаНДСУвел = sfdoc.СуммаНДС + summ_sub_nds;
//fmCAVTInvoiceBase invoice = os.FindObject<fmCAVTInvoiceBase>(
// XPQuery<fmCAVTInvoiceBase>.TransformExpression(
// ((ObjectSpace)os).Session,
// rec => rec.Supplier.Code == imp_rec.SF_VO_CODE &&
// rec.RegNumber == imp_rec.SF_INT_NUMBER &&
// rec.Date >= sf_date &&
// rec.Date < sf_date.AddDays(1)
// ));
////if (invoice == null) {
//// count++;
//// System.Console.WriteLine(imp_rec.SF_INT_NUMBER + " " + imp_rec.SF_NUMBER + " " + imp_rec.SF_DATE + " " + summ_cost + " " + summ_nds);
////}
//if (invoice == null) {
// crmCParty party = os.GetObjects<crmCParty>(new BinaryOperator("Code", imp_rec.SF_VO_CODE)).FirstOrDefault();
// invoice = os.CreateObject<fmCAVTInvoiceBase>();
// invoice.RegNumber = imp_rec.SF_INT_NUMBER;
// invoice.Number = imp_rec.SF_NUMBER;
// invoice.Date = sf_date;
// invoice.Supplier = party;
// invoice.Customer = register.Party;
// invoice.SummAVT = Decimal.Parse(imp_rec.SUMM_NDS.Trim().Replace('.', ','));
// invoice.SummCost = Decimal.Parse(imp_rec.SUMM_COST.Trim().Replace('.', ','));
//}
//else {
// invoice.SummAVT = Decimal.Parse(imp_rec.SUMM_NDS.Trim().Replace('.', ','));
// invoice.SummCost = Decimal.Parse(imp_rec.SUMM_COST.Trim().Replace('.', ','));
// fmCAVTInvoiceRegisterLine line_check = os.FindObject<fmCAVTInvoiceRegisterLine>(
// CriteriaOperator.And(new BinaryOperator("InvoiceVersion", invoice.Current)), true);
// if (line_check != null) continue;
//}
//fmCAVTInvoiceRegisterLine line = register.InLines.Where(rec => rec.Invoice == invoice).FirstOrDefault();
//if (line == null) {
// line = os.CreateObject<fmCAVTInvoiceRegisterLine>();
// register.InLines.Add(line);
//}
//line.SequenceNumber = seq_num++;
//// line_doc.DateTransfer = invoice.Date;
//line.Invoice = invoice;
//line.TransferType = inv_transfer_types.Where(rec => rec.Code == "1").FirstOrDefault();
//if (String.IsNullOrEmpty(imp_rec.SF_TRANS_DATE.Trim())) {
// DateTime trans_date = default(DateTime);
// DateTime.TryParseExact(imp_rec.SF_TRANS_DATE.Trim(), "yyyyMMdd", null, System.Globalization.DateTimeStyles.None, out trans_date);
// line.DateTransfer = trans_date;
//}
//if (line.DateTransfer < sf_date)
// line.DateTransfer = sf_date;
//line.OperationType = inv_oper_types.Where(rec => rec.Code == imp_rec.SF_OPER_TYPE.Trim() ||
// rec.Code == '0' + imp_rec.SF_OPER_TYPE.Trim()).FirstOrDefault();
}
System.Console.WriteLine("All " + count);
}
static public void ImportBuhData(IObjectSpace os, fmCAVTInvoiceRegister register)
{
UInt32 seq_num = 1;
IList <fmCAVTInvoiceType> inv_types = os.GetObjects <fmCAVTInvoiceType>();
IList <fmCAVTInvoiceTransferType> inv_transfer_types = os.GetObjects <fmCAVTInvoiceTransferType>();
IList <fmCAVTInvoiceOperationType> inv_oper_types = os.GetObjects <fmCAVTInvoiceOperationType>();
foreach (var invrec in register.BookBuhImport.BookBuhRecords.
Where(rec => rec.BookType == "P" && (rec.RecordType == "PAY" || rec.RecordType == "AIN" || rec.RecordType == "EAT") ||
rec.BookType == "B" && (rec.RecordType == "PAY")).
GroupBy(rec => new { rec.AVTInvoicePartyCode, rec.AVTInvoiceType, rec.AVTInvoiceNumber, rec.AVTInvoiceDate }).
OrderBy(rec => rec.Key.AVTInvoiceDate.ToString("yyyyMMdd") + rec.Key.AVTInvoiceNumber))
{
if (String.IsNullOrEmpty(invrec.Key.AVTInvoiceNumber.Trim()) || String.IsNullOrEmpty(invrec.Key.AVTInvoicePartyCode.Trim()))
{
continue;
}
if (invrec.Key.AVTInvoiceType != "явт" && invrec.Key.AVTInvoiceType != "ятю" && invrec.Key.AVTInvoiceType != "ятб")
{
continue;
}
crmCParty party = os.GetObjects <crmCParty>(new BinaryOperator("Code", invrec.Key.AVTInvoicePartyCode)).FirstOrDefault();
fmCAVTInvoiceBase invoice = os.FindObject <fmCAVTInvoiceBase>(
CriteriaOperator.And(new BinaryOperator("Number", invrec.Key.AVTInvoiceNumber),
new BinaryOperator("Date", invrec.Key.AVTInvoiceDate.Date, BinaryOperatorType.GreaterOrEqual),
new BinaryOperator("Date", invrec.Key.AVTInvoiceDate.Date.AddDays(1), BinaryOperatorType.Less),
new BinaryOperator("Supplier", register.Party)), true
// , new BinaryOperator("Customer", party))
);
if (invoice == null && invrec.Key.AVTInvoiceType == "явт")
{
invoice = os.CreateObject <fmCAVTInvoiceBase>();
invoice.Number = invrec.Key.AVTInvoiceNumber;
invoice.Date = invrec.Key.AVTInvoiceDate;
invoice.Supplier = register.Party;
invoice.Customer = party;
}
if (invoice != null)
{
if (invoice.InvoiceType == null && invoice.Number != null && invoice.Number.Length > 0)
{
foreach (fmCAVTInvoiceType inv_type in inv_types)
{
if (inv_type.InvoiceDirection == fmAVTInvoiceDirection.AVTInvoiceOut &&
inv_type.Prefix == invoice.Number.Substring(0, 1))
{
invoice.InvoiceType = inv_type;
}
}
}
fmCAVTInvoiceRegisterLine line_check = os.FindObject <fmCAVTInvoiceRegisterLine>(
CriteriaOperator.And(new BinaryOperator("InvoiceVersion", invoice.Current)), true);
if (line_check != null)
{
continue;
}
// if (invrec.Key.AVTInvoiceType != "ятб") {
// foreach (fmCAVTBookBuhRecord rec in invrec) {
// invoice.SummAVT = invoice.SummAVT + rec.SummVAT;
// invoice.SummCost = invoice.SummCost + rec.SummAll - rec.SummVAT;
// }
// }
if (invoice.Customer == null)
{
invoice.Customer = party;
}
if (invrec.Key.AVTInvoiceNumber.StartsWith("5"))
{
invoice.Customer = invoice.Customer;
}
if (invoice.SummAVT == 0 && invoice.SummCost == 0)
{
foreach (fmCAVTBookBuhRecord rec in invrec)
{
decimal summ_vat = rec.SummVAT + rec.SummVATCost + rec.SummVATCost + rec.SummVATExp;
invoice.SummAVT = invoice.SummAVT + summ_vat;
if (rec.NDSRate == "2")
{
invoice.SummCost = invoice.SummCost + Decimal.Round(summ_vat * 100m / 18m, 2);
}
else if (rec.NDSRate == "3")
{
invoice.SummCost = invoice.SummCost + Decimal.Round(summ_vat * 100m / 10m, 2);
}
}
}
fmCAVTInvoiceRegisterLine line = register.OutLines.Where(rec => rec.Invoice == invoice).FirstOrDefault();
if (line == null)
{
line = os.CreateObject <fmCAVTInvoiceRegisterLine>();
register.OutLines.Add(line);
}
line.SequenceNumber = seq_num++;
line.DateTransfer = invoice.Date;
line.Invoice = invoice;
line.TransferType = inv_transfer_types.Where(rec => rec.Code == "1").FirstOrDefault();
if (invrec.Key.AVTInvoiceType == "явт")
{
line.OperationType = inv_oper_types.Where(rec => rec.Code == "01").FirstOrDefault();
}
if (invrec.Key.AVTInvoiceType == "ятю")
{
line.OperationType = inv_oper_types.Where(rec => rec.Code == "02").FirstOrDefault();
}
if (invrec.Key.AVTInvoiceType == "ятб")
{
if (invrec.Where(rec => rec.RecordType == "SMN").Count() != 0)
{
line.OperationType = inv_oper_types.Where(rec => rec.Code == "08").FirstOrDefault();
}
if (invrec.Where(rec => rec.RecordType == "EAT").Count() != 0)
{
line.OperationType = inv_oper_types.Where(rec => rec.Code == "07").FirstOrDefault();
}
}
}
}
seq_num = 1;
foreach (var invrec in register.BookBuhImport.BookBuhRecords.
Where(rec => rec.BookType == "B" && (rec.RecordType == "BAY" || rec.RecordType == "AON") ||
rec.BookType == "P" && (rec.RecordType == "BAY")).
GroupBy(rec => new { rec.AVTInvoicePartyCode, rec.AVTInvoiceType, rec.AVTInvoiceRegNumber, rec.AVTInvoiceNumber, rec.AVTInvoiceDate }).
OrderBy(rec => rec.Key.AVTInvoiceDate.ToString("yyyyMMdd") + rec.Key.AVTInvoiceRegNumber))
{
if (String.IsNullOrEmpty(invrec.Key.AVTInvoiceNumber.Trim()) || String.IsNullOrEmpty(invrec.Key.AVTInvoicePartyCode.Trim()))
{
continue;
}
if (invrec.Key.AVTInvoiceType != "явт" && invrec.Key.AVTInvoiceType != "ятб")
{
continue;
}
crmCParty party = os.FindObject <crmCParty>(new BinaryOperator("Code", invrec.Key.AVTInvoicePartyCode));
fmCAVTInvoiceBase invoice = os.FindObject <fmCAVTInvoiceBase>(
CriteriaOperator.And(new BinaryOperator("Number", invrec.Key.AVTInvoiceNumber),
new BinaryOperator("Date", invrec.Key.AVTInvoiceDate.Date, BinaryOperatorType.GreaterOrEqual),
new BinaryOperator("Date", invrec.Key.AVTInvoiceDate.Date.AddDays(1), BinaryOperatorType.Less),
new BinaryOperator("Supplier", party)), true
// , new BinaryOperator("Customer", party))
);
//
if (invoice == null)
{
invoice = os.CreateObject <fmCAVTInvoiceBase>();
invoice.RegNumber = invrec.Key.AVTInvoiceRegNumber;
invoice.Number = invrec.Key.AVTInvoiceNumber;
invoice.Date = invrec.Key.AVTInvoiceDate;
invoice.Supplier = party;
invoice.Customer = register.Party;
}
else
{
fmCAVTInvoiceRegisterLine line_check = os.FindObject <fmCAVTInvoiceRegisterLine>(
CriteriaOperator.And(new BinaryOperator("InvoiceVersion", invoice.Current)), true);
if (line_check != null)
{
continue;
}
}
Decimal SummVAT_18 = invrec.Where(buhrec => buhrec.NDSRate == "2").Sum(buhrec => buhrec.SummVATCost + buhrec.SummVATExp + buhrec.SummVAT);
Decimal SummVAT_10 = invrec.Where(buhrec => buhrec.NDSRate == "3").Sum(buhrec => buhrec.SummVATCost + buhrec.SummVATExp + buhrec.SummVAT);
Decimal SummNoVAT = invrec.Where(buhrec => buhrec.NDSRate == "5").Sum(buhrec => buhrec.SummAll);
// Decimal SummCost_18 = Decimal.Round(SummVAT_18 * 100m / 18m, 2);
// Decimal SummCost_10 = Decimal.Round(SummVAT_10 * 100m / 10m, 2);
// SummCost_NoVAT = SummCost_NoVAT +
// Decimal.Round(SummNoVAT_18 * 118m / 18m, 2) +
// Decimal.Round(SummNoVAT_10 * 110m / 10m, 2);
invoice.SummAVT = SummVAT_18 + SummVAT_10;
invoice.SummCost = SummNoVAT +
Decimal.Round(SummVAT_18 * 100m / 18m, 2) +
Decimal.Round(SummVAT_10 * 100m / 10m, 2);
fmCAVTInvoiceRegisterLine line = register.InLines.Where(rec => rec.Invoice == invoice).FirstOrDefault();
if (line == null)
{
line = os.CreateObject <fmCAVTInvoiceRegisterLine>();
register.InLines.Add(line);
}
line.SequenceNumber = seq_num++;
// line_doc.DateTransfer = invoice.Date;
line.Invoice = invoice;
line.TransferType = inv_transfer_types.Where(rec => rec.Code == "1").FirstOrDefault();
line.DateTransfer = invrec.Select(rec => rec.BuhDocDate).Min();
if (line.DateTransfer < DateTime.ParseExact("20130101", "yyyyMMdd", null))
{
line.DateTransfer = DateTime.ParseExact("20130131", "yyyyMMdd", null);
}
if (line.DateTransfer < line.Invoice.Date)
{
line.DateTransfer = line.Invoice.Date;
}
if (invrec.Key.AVTInvoiceType == "явт")
{
line.OperationType = inv_oper_types.Where(rec => rec.Code == "01").FirstOrDefault();
}
if (invrec.Key.AVTInvoiceType == "ятб")
{
if (invrec.Where(rec => rec.RecordType == "AON").Count() != 0)
{
line.OperationType = inv_oper_types.Where(rec => rec.Code == "02").FirstOrDefault();
}
if (invrec.Where(rec => rec.RecordType == "SMN").Count() != 0)
{
line.OperationType = inv_oper_types.Where(rec => rec.Code == "08").FirstOrDefault();
}
if (invrec.Where(rec => rec.RecordType == "EAT").Count() != 0)
{
line.OperationType = inv_oper_types.Where(rec => rec.Code == "07").FirstOrDefault();
}
}
}
}
public void import(String filename)
{
// CsvEngine engine = new CsvEngine(new CsvOptions("ImportPartyRecord", ';',6));
// engine.Options.IgnoreFirstLines = 1;
// Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection("Server=npomash;User Id=pg_adm;Password='******';Database=ermdev;Encoding=UNICODE;");
// IXpoDataStoreProvider dsp = new ConnectionDataStoreProvider(con);
// IDbConnection con = PostgreSqlConnectionProvider.CreateConnection("Server=npomash;User Id=pg_adm;Password='******';Database=ermdev;Encoding=UNICODE;");
IDbConnection con = PostgreSqlConnectionProvider.CreateConnection("Server=alt-dev;User Id=pg_adm;Password='******';Database=ermprod;Encoding=UNICODE;");
//
IDataLayer data_layer = XpoDefault.GetDataLayer(con, AutoCreateOption.None);
IObjectLayer object_layer = new SimpleObjectLayer(data_layer);
Int32 count = 0;
// ObjectSpaceProvider osp = new ObjectSpaceProvider(dsp);
//
// XafApplication application = new ImportApplication();
// ModuleBase testModule = new ModuleBase();
// testModule.AdditionalExportedTypes.Add(typeof(crmCParty));
// testModule.AdditionalExportedTypes.Add(typeof(crmCBusinessman));
// testModule.AdditionalExportedTypes.Add(typeof(crmCPhysicalParty));
// testModule.AdditionalExportedTypes.Add(typeof(crmCLegalPerson));
// application.Modules.Add(testModule);
// application.Setup("ImportApplication", osp);
// using (IObjectSpace os = application.CreateObjectSpace()) {
using (UnitOfWork uow_base = new UnitOfWork(object_layer)) {
DataTable tbl = (new CsvEngine("ImportPartyRecord", ';', 6)).ReadFileAsDT(filename);
foreach (DataRow row in tbl.Rows)
{
using (UnitOfWork uow = uow_base.BeginNestedUnitOfWork()) {
ImportPartyRecord rec = new ImportPartyRecord();
rec.Type = ((String)row.ItemArray[0]).Trim();
rec.Code = ((String)row.ItemArray[1]).Trim();
rec.INN = ((String)row.ItemArray[2]).Trim();
rec.KPP = ((String)row.ItemArray[3]).Trim();
rec.Name = ((String)row.ItemArray[4]).Trim();
rec.Addr = ((String)row.ItemArray[5]).Trim();
System.Console.WriteLine(rec.Type + "@" + rec.Code + "@" + rec.INN + "@" + rec.KPP + "@" + rec.Name + "@" + rec.Addr);
XPQuery <crmCParty> q_party = new XPQuery <crmCParty>(uow, true);
crmCParty party = q_party.FirstOrDefault(obj => obj.Code == rec.Code);
if (party != null)
{
continue;
}
switch (rec.Type)
{
case "ИП":
crmCBusinessman bm = new crmCBusinessman(uow);
bm.Code = rec.Code;
bm.Name = rec.Name;
bm.INN = rec.INN;
bm.AddressLegal.AddressHandmake = rec.Addr;
bm.AddressFact.AddressHandmake = rec.Addr;
break;
case "ФЛ":
crmCPhysicalParty phl = new crmCPhysicalParty(uow);
phl.Code = rec.Code;
phl.Name = rec.Name;
phl.INN = rec.INN;
phl.AddressLegal.AddressHandmake = rec.Addr;
phl.AddressFact.AddressHandmake = rec.Addr;
break;
default:
crmCLegalPerson lp = new crmCLegalPerson(uow);
lp.Code = rec.Code;
lp.Name = rec.Name;
lp.INN = rec.INN;
lp.KPP = rec.KPP;
lp.AddressLegal.AddressHandmake = rec.Addr;
lp.AddressFact.AddressHandmake = rec.Addr;
break;
}
uow.CommitChanges();
count++;
}
}
uow_base.CommitChanges();
System.Console.WriteLine("All count: " + count);
}
}
public virtual void AutoBinding(crmBankAccount BankAccount, fmCPRPaymentRequest PaymentRequest, fmCPRRepaymentTask RepaymentTask)
{
// Привязка с распределением счетов
// Проверка на уже связанность заявки
XPQuery <fmCPRRepaymentJurnal> repaymentJurnalExists = new XPQuery <fmCPRRepaymentJurnal>(this.Session);
var queryRepaymentJurnalExists = from repaymentJurnal in repaymentJurnalExists
where repaymentJurnal.PaymentDocument == this
select repaymentJurnal;
if (queryRepaymentJurnalExists.Count() > 0)
{
DevExpress.XtraEditors.XtraMessageBox.Show("Заявка уже привязана");
return;
}
// Проверка на превышение сумм
if (RepaymentTask.RepaymentRegisterSum + PaymentRequest.Summ > RepaymentTask.OperationRegisterSum)
{
DevExpress.XtraEditors.XtraMessageBox.Show("Сумма связанных заявок превысит сумму по документам выписки. Привязка невозможна.");
return;
}
// Наша организация
crmCParty OurParty = GetOurParty();
// Привязка в принципе возможна, т.е. выбранная заявка добавляется в fmCPRRepaymentJurnal
fmCPRRepaymentJurnal newRepaymentJournalRecord = new fmCPRRepaymentJurnal(Session);
newRepaymentJournalRecord.BankAccount = BankAccount;
//newRepaymentJournalRecord.PaymentDate =
newRepaymentJournalRecord.PaymentDocument = this;
newRepaymentJournalRecord.PaymentRequest = PaymentRequest;
newRepaymentJournalRecord.PlaneFact = CRM.Contract.Analitic.PlaneFact.FACT;
if (OurParty.INN == PaymentRequest.PartyPayReceiver.INN)
{
newRepaymentJournalRecord.SumIn = PaymentRequest.Summ;
}
if (OurParty.INN == PaymentRequest.PartyPaySender.INN)
{
newRepaymentJournalRecord.SumOut = PaymentRequest.Summ;
}
newRepaymentJournalRecord.ValutaObligation = PaymentRequest.Valuta; // Валюта обязательств
newRepaymentJournalRecord.ValutaPayment = PaymentRequest.PaymentValuta; // Валюта платежа
// Обновление таблицы RepaymentTaskLines
// Удаление всех записей из fmCPRRepaymentTaskLine
Session.Delete(RepaymentTask.RepaymentTaskLines);
//Session.PurgeDeletedObjects();
// Журнал привязок
XPQuery <fmCPRRepaymentJurnal> repaymentJurnals = new XPQuery <fmCPRRepaymentJurnal>(this.Session);
var queryRepaymentJurnals = from repaymentJurnal in repaymentJurnals
where repaymentJurnal.PaymentDocument == this
select repaymentJurnal;
var queryGroupRepaymentJurnals = from repaymentJurnal in queryRepaymentJurnals
group repaymentJurnal by new { repaymentJurnal.PaymentRequest }
into grj
select new {
PaymentRequest = grj.Key.PaymentRequest,
GroupSumIn = grj.Sum(row => row.SumIn),
GroupSumOut = grj.Sum(row => row.SumOut)
};
foreach (var grj in queryGroupRepaymentJurnals)
{
// Добавляем запись в fmCPRRepaymentTaskLine
fmCPRRepaymentTaskLine newLine = new fmCPRRepaymentTaskLine(Session);
newLine.RepaymentTask = RepaymentTask;
RepaymentTask.RepaymentTaskLines.Add(newLine);
newLine.PaymentRequest = PaymentRequest;
newLine.RequestSum = grj.GroupSumIn + grj.GroupSumOut; // Одна из них равна 0
}
}
