public List <COMING_SAP> GetInvoice(Organization currentOrganization, StorageName currentStorage, ICriteriaRepository <Nomenclature> nomenRepository, ICriteriaRepository <NomGroup> nomGroupRepository,
int DocTypeId, string docNumber, int docYear, string docDate, out string Message)
{
List <COMING_SAP> model = new List <COMING_SAP>();
IFormatProvider culture = new System.Globalization.CultureInfo("ru-RU", true);
Dictionary <string, Object> param = new Dictionary <string, Object>();
Message = "OK";
string paramPrefix = DbPapamPrefix;
int rowCount = 0;
IDbConnection dbConnection = this.Session.Connection;
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var cmd = dbConnection.CreateCommand())
{
cmd.CommandText = "select * from invoice where DocNumber=" + paramPrefix + "DocNumber and InvoiceDate>=" + paramPrefix + "DocDate1 and InvoiceDate<=" + paramPrefix + "DocDate2 and StorageNameExternalCode=" + paramPrefix + "StorageCode" + (docDate != "" ? " and InvoiceDate=" + paramPrefix + "DocDate" : "");
cmd.CommandType = CommandType.Text;
var inval = cmd.CreateParameter();
inval.ParameterName = "DocNumber";
inval.DbType = DbType.String;
inval.Value = docNumber;
cmd.Parameters.Add(inval);
inval = cmd.CreateParameter();
inval.ParameterName = "DocDate1";
inval.DbType = DbType.Date;
inval.Value = DateTime.ParseExact("01.01." + docYear, "dd.MM.yyyy", culture);
cmd.Parameters.Add(inval);
inval = cmd.CreateParameter();
inval.ParameterName = "DocDate2";
inval.DbType = DbType.Date;
inval.Value = DateTime.ParseExact("31.12." + docYear, "dd.MM.yyyy", culture);
cmd.Parameters.Add(inval);
inval = cmd.CreateParameter();
inval.ParameterName = "StorageCode";
inval.DbType = DbType.String;
inval.Value = currentStorage.Externalcode;
cmd.Parameters.Add(inval);
if (docDate != "")
{
inval = cmd.CreateParameter();
inval.ParameterName = "DocDate";
inval.DbType = DbType.Date;
inval.Value = DateTime.ParseExact(docDate, "dd.MM.yyyy", culture);
cmd.Parameters.Add(inval);
}
try
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
rowCount++;
COMING_SAP item = new COMING_SAP();
item.DocNumber = Convert.ToString(reader["DOCNUMBER"]);
if (reader["INVOICEDATE"] != null)
{
item.DocDate = ((DateTime)reader["INVOICEDATE"]).ToString(DataGlobals.DATE_FORMAT_FULL_YEAR);
}
else
{
Message = "В накладной " + docNumber + " отсутствует дата!";
}
item.StorageNameExternalCode = Convert.ToString(reader["STORAGENAMEEXTERNALCODE"]);
item.StorageName = Convert.ToString(reader["STORAGENAME"]);
item.ExternalCode = Convert.ToString(reader["EXTERNALCODE"]);
item.QUANTITY = Convert.ToInt32(reader["QUANTITY"]);
item.DocTypeId = DocTypeId;
model.Add(item);
}
reader.Close();
}
}
catch (Exception e)
{
Message = e.Message;
}
}
if (model.GroupBy(g => g.DocNumber, g => g.DocDate).Count() > 1)
{
Message = "С номером " + model[0].DocNumber + " несколько документов!!! Укажите точную дату документа";
model.Clear();
}
else
{
param.Clear();
param.Add("Organization.Id", currentOrganization.Id);
foreach (var item in model)
{
param["ExternalCode"] = item.ExternalCode;
//param.Add("IsActive", true);
IList <Nomenclature> nomenclatures = nomenRepository.GetByLikeCriteria(param);
if (nomenclatures.Count != 0)
{
Nomenclature nomenclature = nomenclatures[0];
item.MaterialId = nomenclature.ExternalCode;
if (nomenclature.Growth != null)
{
item.GrowthId = nomenclature.Growth.Id;
item.GrowthName = nomenclature.Growth.SizeNumber;
}
item.IsWinter = nomenclature.NomGroup.IsWinter;
item.MATERIAL = nomenclature.Name;
item.NomBodyPartId = nomenclature.NomBodyPart.Id;
item.NomBodyPartName = nomenclature.NomBodyPart.Name;
item.NomGroupId = nomenclature.NomGroup.Id;
item.NomGroupName = nomenclature.NomGroup.Name;
item.SexId = nomenclature.Sex.Id;
item.SexName = nomenclature.Sex.Name;
if (nomenclature.NomBodyPartSize != null)
{
item.SizeId = nomenclature.NomBodyPartSize.Id;
item.SizeName = nomenclature.NomBodyPartSize.SizeNumber;
}
item.UnitId = nomenclature.Unit.Id;
item.UnitName = nomenclature.Unit.Name;
}
else
{
item.SexName = "";
}
}
}
return(model);
}
public List <COMING_SAP> GetInvoice(Organization currentOrganization, StorageName currentStorage, ICriteriaRepository <Nomenclature> nomenRepository, ICriteriaRepository <NomGroup> nomGroupRepository, int DocTypeId, string docNumber, int docYear, string docDate, out string Message)
{
String INCOMING_RFC = ConfigurationManager.AppSettings["SAP_ERP_" + currentOrganization.Id];
RfcDestination destination = RfcDestinationManager.GetDestination(INCOMING_RFC);
IRfcFunction function = null;
List <COMING_SAP> list = new List <COMING_SAP>();
// string docDate = "01.01." + docYear;
docDate = "01.01." + docYear;
if (currentStorage == null)
{
currentStorage = new StorageName();
}
try
{
function = destination.Repository.CreateFunction("BAPI_GOODSMVT_GETDETAIL");
function.SetValue("MATERIALDOCUMENT", docNumber);
function.SetValue("MATDOCUMENTYEAR", docYear);
function.Invoke(destination);
}
catch (RfcBaseException e)
{
Message = e.StackTrace;
// Console.WriteLine(e.ToString());
}
Dictionary <string, Nomenclature> MaterialName = new Dictionary <string, Nomenclature>();
Dictionary <string, string> NomenclatureGroupName = new Dictionary <string, string>();
Message = "OK";
IRfcStructure tableHeader = function.GetStructure("GOODSMVT_HEADER");
if (tableHeader.Count > 0)
{
if (tableHeader.GetString("DOC_DATE") != "0000-00-00")
{
docDate = tableHeader.GetString("DOC_DATE");
try
{
docDate = DateTime.ParseExact(docDate, "yyyy-MM-dd", new System.Globalization.CultureInfo("ru-RU", true)).ToString(DataGlobals.DATE_FORMAT_FULL_YEAR);
}
catch
{
}
}
}
IRfcTable table = function.GetTable("GOODSMVT_ITEMS");
if (table.RowCount == 0)
{
IRfcTable retTable = function.GetTable("RETURN");
retTable.CurrentIndex = 0;
Message = retTable.GetString("MESSAGE");
}
else
{
for (int i = 0; i < table.RowCount; i++)
{
table.CurrentIndex = i;
if ((table.GetString("PLANT") == "" + currentStorage.Plant) && (table.GetString("STGE_LOC") == "" + currentStorage.Externalcode))
{
//if (getKnownMove(table.GetString("MOVE_TYPE"), currentStorage) != MoveType.Unknown)
//{
COMING_SAP incomingSap = new COMING_SAP(i + 1);
incomingSap.DocTypeId = DocTypeId;
incomingSap.DocNumber = docNumber;
//incomingSap.DocDate = "01.01." + docYear;
incomingSap.DocDate = docDate;
incomingSap.MaterialId = table.GetString("MATERIAL").TrimStart('0');
Nomenclature defValue = null;
if (MaterialName.TryGetValue(incomingSap.MaterialId, out defValue))
{
incomingSap.MATERIAL = defValue.Name;
incomingSap.ExternalCode = defValue.ExternalCode;
if (defValue.Growth != null)
{
incomingSap.GrowthId = defValue.Growth.Id;
incomingSap.GrowthName = defValue.Growth.SizeNumber;
}
if (defValue.NomBodyPartSize != null)
{
incomingSap.SizeId = defValue.NomBodyPartSize.Id;
incomingSap.SizeName = defValue.NomBodyPartSize.SizeNumber;
}
if (defValue.NomGroup != null)
{
incomingSap.NomGroupId = defValue.NomGroup.Id;
incomingSap.NomGroupName = defValue.NomGroup.Name;
incomingSap.IsWinter = defValue.NomGroup.IsWinter;
}
if (defValue.Sex != null)
{
incomingSap.SexId = defValue.Sex.Id;
incomingSap.SexName = defValue.Sex.Name;
}
if (defValue.Unit != null)
{
incomingSap.UnitId = defValue.Unit.Id;
incomingSap.UnitName = defValue.Unit.Name;
}
if (defValue.NomBodyPart != null)
{
incomingSap.NomBodyPartId = defValue.NomBodyPart.Id;
incomingSap.NomBodyPartName = defValue.NomBodyPart.Name;
}
}
else
{
Dictionary <string, object> queryParams = new Dictionary <string, object>();
queryParams.Add("Organization", currentOrganization);
queryParams.Add("ExternalCode", incomingSap.MaterialId.TrimStart('0'));
queryParams.Add("IsActive", true);
// Nomenclature currentNomenclature = nomenRepository.FindOne(queryParams);
IList <Nomenclature> currentNomenclatures = nomenRepository.GetByLikeCriteria(queryParams);
Nomenclature currentNomenclature = null;
if (currentNomenclatures.Count > 0)
{
currentNomenclature = currentNomenclatures[0];
}
if (currentNomenclature != null)
{
// TODO: Требуется обновить группу номенклатуры из SAP?
defValue = currentNomenclature;
incomingSap.MATERIAL = defValue.Name;
incomingSap.ExternalCode = defValue.ExternalCode;
if (defValue.NomGroup != null)
{
incomingSap.NomGroupId = defValue.NomGroup.Id;
incomingSap.NomGroupName = defValue.NomGroup.Name;
incomingSap.IsWinter = defValue.NomGroup.IsWinter;
}
if (defValue.Sex != null)
{
incomingSap.SexId = defValue.Sex.Id;
incomingSap.SexName = defValue.Sex.Name;
}
if (defValue.Unit != null)
{
incomingSap.UnitId = defValue.Unit.Id;
incomingSap.UnitName = defValue.Unit.Name;
}
if (defValue.NomBodyPart != null)
{
incomingSap.NomBodyPartId = defValue.NomBodyPart.Id;
incomingSap.NomBodyPartName = defValue.NomBodyPart.Name;
}
}
else
{
//Группу из сапа пока не запрашиваю!!!!
/* defValue = getMaterialName(destination, incomingSap.MaterialId);
* if (NomenclatureGroupName.TryGetValue(incomingSap.NomrnclatureGroupId, out defStringValue))
* {
* incomingSap.NomrnclatureGroupName = defStringValue;
* }
* else
* {
* defStringValue = getNomenclatureGroupName(destination, incomingSap.NomrnclatureGroupId);
* incomingSap.NomrnclatureGroupName = defStringValue;
* NomenclatureGroupName.Add(incomingSap.NomrnclatureGroupId, defStringValue);
* }*/
if (Message == "OK")
{
Message = "";
}
defValue = new Nomenclature(int.Parse(incomingSap.MaterialId));
String[] strArray = getMaterialName(destination, table.GetString("MATERIAL"));
defValue.Name = strArray[0];
defValue.ExternalCode = strArray[1].TrimStart('0');
Message = Message + "Номенклатура: [" + incomingSap.MaterialId + "]" + defValue.Name + "не найдена.\n";
incomingSap.MATERIAL = defValue.Name;
incomingSap.ExternalCode = incomingSap.MaterialId;
queryParams.Clear();
queryParams.Add("Organization", currentOrganization);
queryParams.Add("ExternalCode", strArray[1].TrimStart('0'));
NomGroup nGroup = nomGroupRepository.FindOne(queryParams);
if (nGroup != null)
{
incomingSap.NomGroupId = nGroup.Id;
incomingSap.NomGroupName = nGroup.Name;
incomingSap.IsWinter = nGroup.IsWinter;
incomingSap.NomBodyPartId = nGroup.NomBodyPart.Id;
incomingSap.NomBodyPartName = nGroup.NomBodyPart.Name;
}
incomingSap.SAPNomGroupId = strArray[1].TrimStart('0');
incomingSap.SAPNomGroupName = getNomenclatureGroupName(destination, strArray[1]);
defValue.NomGroup = nGroup;
}
if (defValue != null)
{
MaterialName.Add(incomingSap.MaterialId, defValue);
}
}
incomingSap.QUANTITY = (int)Math.Truncate(table.GetDouble("ENTRY_QNT"));
incomingSap.UOM = table.GetString("ENTRY_UOM");
incomingSap.LC = table.GetString("AMOUNT_LC");
incomingSap.SV = table.GetString("AMOUNT_SV");
incomingSap.MoveType = table.GetString("MOVE_TYPE");
//NomenclatureInfo nInfo = getMaterialDetail(destination, incomingSap.MaterialId);
list.Add(incomingSap);
//}
//else {
// Message = table.GetString("MOVE_TYPE") + " вид движения не поддерживается!";
//}
}
}
}
return(list);
}
