private void MapToCustomerRow(CustomerRow row, CUSTOMER entity)
{
row.Company = entity.CompanyCode;
row.CustID = entity.CustomerCode;
row.CustomerType = entity.CustomerType;
row.Name = entity.CustomerName;
row.Address1 = entity.Address;
row.Address2 = entity.Ward;
row.Address3 = entity.District;
row.City = entity.City;
row.State = entity.Region;
row.Country = entity.Country;
row.SalesRepCode = entity.SalesRepCode;
row.PhoneNum = entity.Phone1;
row.FaxNum = entity.Fax;
row.TaxRegionCode = entity.GroupTax;
row.ResaleID = entity.TaxCode;
row.CurrencyCode = entity.Currency;
row.FederalID = entity.BusinessRegistrationCertificate;
row.ShipViaCode = entity.ShipViaCode;
row.CreditLimit = entity.CreditLimit;
row.TermsCode = entity.PaymentTerm;
row.TerritoryID = entity.TerritoryID;
row.ShipToNum = "ST" + entity.CustomerCode;
row.UserDefinedColumns["Character01"] = entity.FullAddress;
}
public CustomerRow AddCustomerRow(string firstName, string lastName, string userName)
{
CustomerRow rowCustomerRow = ((CustomerRow)(this.NewRow()));
object[] columnValuesArray = new object[] {
firstName,
lastName,
userName
};
rowCustomerRow.ItemArray = columnValuesArray;
this.Rows.Add(rowCustomerRow);
return(rowCustomerRow);
}
public CustomerRow AddCustomerRow(string Company, System.DateTime Contacted, string FirstName, string LastName, double Phone, string UserCode)
{
CustomerRow rowCustomerRow = ((CustomerRow)(this.NewRow()));
object[] columnValuesArray = new object[] {
Company,
Contacted,
FirstName,
LastName,
Phone,
UserCode
};
rowCustomerRow.ItemArray = columnValuesArray;
this.Rows.Add(rowCustomerRow);
return(rowCustomerRow);
}
public IEnumerable GetData()
{
var fld = CustomerRow.Fields;
var row = new CustomerRow();
var query = new SqlQuery().From(row).SelectTableFields();
if (!OrnekIcerir.IsEmptyOrNull())
{
query.Where(fld.Name.Contains(OrnekIcerir));
}
using (var connection = SqlConnections.NewByKey("Default"))
{
return(connection.Query <Item>(query));
}
}
public CustomerRow AddCustomerRow(string FirstName, string LastName, string CustomerStatus, string Address, string City, System.Double CRate, string Email, string Fax, int Phone, int Zipcode)
{
CustomerRow rowCustomerRow = ((CustomerRow)(this.NewRow()));
rowCustomerRow.ItemArray = new object[] {
null,
FirstName,
LastName,
CustomerStatus,
Address,
City,
CRate,
Email,
Fax,
Phone,
Zipcode
};
this.Rows.Add(rowCustomerRow);
return(rowCustomerRow);
}
public CustomerRow AddCustomerRow(System.Guid CustomerID, string FirstName, string LastName, string MiddleName, string Address, string Apartment, string City, string State, string Zip, string HomePhone, string BusinessPhone, string FullName)
{
CustomerRow rowCustomerRow = ((CustomerRow)(this.NewRow()));
rowCustomerRow.ItemArray = new object[] {
CustomerID,
FirstName,
LastName,
MiddleName,
Address,
Apartment,
City,
State,
Zip,
HomePhone,
BusinessPhone,
FullName
};
this.Rows.Add(rowCustomerRow);
return(rowCustomerRow);
}
AddInvoiceRow(Guid InvoiceID,
DateTime InvoiceDate,
string Terms,
string FOB,
string PO,
CustomerRow parentCustomerRowByCustomerInvoice)
{
if (DoesCustomerHaveCredit())
{
return((InheritedInvoiceRow)base.AddInvoiceRow(InvoiceID,
InvoiceDate,
Terms,
FOB,
PO,
parentCustomerRowByCustomerInvoice));
}
else
{
throw new Exception("Customer Invoice cannot be created, " +
"no credit available");
}
}
public void RemoveCustomerRow(CustomerRow row) {
this.Rows.Remove(row);
}
public void AddCustomerRow(CustomerRow row) {
this.Rows.Add(row);
}
public InvoiceRow AddInvoiceRow(System.Guid InvoiceID, System.DateTime InvoiceDate, string Terms, string FOB, string PO, CustomerRow parentCustomerRowByCustomerInvoice)
{
InvoiceRow rowInvoiceRow = ((InvoiceRow)(this.NewRow()));
rowInvoiceRow.ItemArray = new object[] {
InvoiceID,
null,
InvoiceDate,
Terms,
FOB,
PO,
parentCustomerRowByCustomerInvoice[0]
};
this.Rows.Add(rowInvoiceRow);
return(rowInvoiceRow);
}
public CustomerRowChangeEvent(CustomerRow row, DataRowAction action)
{
this.eventRow = row;
this.eventAction = action;
}
public void RemoveCustomerRow(CustomerRow row)
{
this.Rows.Remove(row);
}
public ExcelMassUploadImportResponse UploadDataClosing(IUnitOfWork uow, ExcelMassUploadImportRequest request)
{
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var response = new ExcelMassUploadImportResponse();
response.ErrorList = new List <string>();
// Get batch of row number/index
var numberList = request.TotalRecord;
var p = MyRow.Fields;
int receivedCount = 0;
/////////////////////////////////////////////////////////////
//ITEM INFO UPLOAD
/////////////////////////////////////////////////////////////
var wsItemInfo = ep.Workbook.Worksheets[1];
for (var currRow = 4; currRow <= (numberList + 4); currRow++)
{
try
{
var odr = new OrderRow();
try
{
CustomerRow custNew = new CustomerRow();
custNew.CustomerName = wsItemInfo.Cells[currRow, 2].Value.ToString();
custNew.Address = "Bekasi";
SaveResponse savedCust = new CustomerRepository().Create(uow, new SaveRequest <CustomerRow>()
{
Entity = custNew
});
odr.CustomerId = int.Parse(savedCust.EntityId.ToString());
odr.Ongkir = 9000;
odr.OrderStatus = "NewOrder";
odr.DetailList = new List <OrderDetailRow>();
odr.DetailList.Add(new OrderDetailRow()
{
ProductId = 1, Quantity = 1, Price = 125000
});
SaveResponse savedOdr = new OrderRepository().Create(uow, new SaveRequest <OrderRow>()
{
Entity = odr
});
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
catch (Exception x)
{
string msg = "Row:" + currRow.ToString() + " - " + x.Message;
response.ErrorList.Add(x.Message);
}
}
response.Inserted = receivedCount;
return(response);
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
//-------------------------- Gather Excel File Data ------------------------------------------------------------//
request.CheckNotNull();
var fName = request.FileName;
Check.NotNullOrWhiteSpace(fName, "filename");
UploadHelper.CheckFileNameSecurity(fName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(fName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var response = new ExcelImportResponse();
var myErrors = response.ErrorList = new List <string>();
/*Read the first Excell sheet and then gather the headers of the import file*/
var worksheet = ep.Workbook.Worksheets.First();
var wsStart = worksheet.Dimension.Start;
var wsEnd = worksheet.Dimension.End;
var headers = worksheet.Cells[wsStart.Row, wsStart.Column, 1, wsEnd.Column];
//-------------------------- Gather Mapping nformation ------------------------------------------------------------//
/*A few variables to make our life easier*/
var myConnection = uow.Connection;
var myFields = MyRow.Fields;
List <string> importedHeaders = new List <string>(); //Headers from Imported File
List <object> importedValues = new List <object>(); //Values being Imported
List <string> systemHeaders = new List <string>(); //Headers currently in system
List <string> sysHeader = new List <string>(); //System Header to import
List <string> exceptionHeaders = new List <string>(); //Haders to not check for during import.
object obj = null; //Object container for value being imported
dynamic a = null; //Handled object to assign to system
string fieldTitle = ""; //Title of field being imported
jImpHelp.entryType entType; //Type of handler to use.
/*Add Imported file headers to proper list*/
foreach (var q in headers)
{
importedHeaders.Add(q.Text);
}
/* Add system headers to proper list while also adding 'ID' to the list. 'ID'
* is the key field from exported files and needs to be mapped manually */
systemHeaders.Add("ID");
foreach (var t in myFields)
{
systemHeaders.Add(t.Title);
}
;
/* Not all columns will be expected to be imported. To avoid unnecesary error messages
* we add the titles of the fields we want ignored here.*/
exceptionHeaders.Add(myFields.AddressLogId.Title);
/* Using the systemHeaders to compare against the importedHeaders, we build an index with
* the column location and match it to the system header using a Dictionary<string, int>. */
Dictionary <string, int> headerMap = myImpHelp.myExcelHeaderMap(importedHeaders, systemHeaders, myErrors, exceptionHeaders);
for (var row = 2; row <= wsEnd.Row; row++)
{
try
{
/* This instance checks the ID field as to whether the row exists or not. if the
* ID key exists, it will use it to update the row with the imported fields but if
* it does not exist, it creates a new entry. */
var sysKey = myFields.CustomerId;
obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, "ID").Value, worksheet);
var wsKeyField = Convert.ToInt32(obj);
var currentRow = myConnection.TryFirst <MyRow>(q => q.Select(sysKey).Where(sysKey == wsKeyField));
if (currentRow == null) //Create New if Row doesnt' exist
{
currentRow = new MyRow()
{
}
}
;
else
{
currentRow.TrackWithChecks = false;
}
/* We now need to handle how we want to manage the imported fields. We list the fields
* being imported using the same code set but we update the entType, fieldTitle and then
* designate what field will be updated. You handle specialty case handlers such as joint
* fields in another file. Do note that I use importedValues and sysHeader to pass on the
* values to the handler. I did this on purpose so that you can pass multiple values if
* neccesary. For example, if your joint field requires 2 values to be entered, you can
* simply capture the value with an additional .Add()
*
* Example :
*
* -------Simple Field
* entType = jImpHelp.entryType.String; //designate the type of item
* fieldTitle = myFields.firstField.Title; //designate the field to be looked at
* obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
* ----ADD OBJECT TO NEW CONTAINER--
* var newContainer = obj;
* ----------------------
* if (obj != null)
* {
* importedValues.Add(obj);
* sysHeader.Add(fieldTitle);
* a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
* if (a != null)
* {
* currentRow.CustomerAddress = a; //designate the field to be updated in the system
* }
* sysHeader.Clear();
* importedValues.Clear();
* }
* -----Joint Field
* entType = jImpHelp.entryType.jointField; //<--Update Me according to type of field to merge with
* fieldTitle = myFields.secondFieldCustomerName.Title;//<--Update Me
* obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
* if (obj != null)
* {
* importedValues.Add(obj);
* -----ADD CAPTURED CONTAINER TO VALUES LIST-----
* importedValue.Add(newContainer)
* -------------------------------------------
* sysHeader.Add(fieldTitle);
* a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
* if (a != null)
* {
* currentRow.AddressLogId = a; ////<--Update Me. *Special Case. Notice: Not the same as field to match
* }
* sysHeader.Clear();
* importedValues.Clear();
* }
*/
//--------------------------Merge Imported Fields ------------------------------------------------------------//
entType = jImpHelp.entryType.String; //designate the type of item
fieldTitle = myFields.CustomerAddress.Title; //designate the field to be looked at
obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
if (obj != null)
{
importedValues.Add(obj);
sysHeader.Add(fieldTitle);
a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
if (a != null)
{
currentRow.CustomerAddress = a; //designate the field to be updated in the system
}
sysHeader.Clear();
importedValues.Clear();
}
/*Same as above, just updated for the next field. */
entType = jImpHelp.entryType.String; //<--Update Me according to type of field to merge with
fieldTitle = myFields.CustomerName.Title; //<--Update Me
obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
if (obj != null)
{
importedValues.Add(obj);
sysHeader.Add(fieldTitle);
a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
if (a != null)
{
currentRow.CustomerName = a; //<--Update Me
}
sysHeader.Clear();
importedValues.Clear();
}
entType = jImpHelp.entryType.CategoryJoin; //<--Update Me *Special Case Joint Field
fieldTitle = myFields.AddressFloor.Title; //<--Update Me *Special Case. Notice: Field to match in import file
obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
if (obj != null)
{
importedValues.Add(obj);
sysHeader.Add(fieldTitle);
a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
if (a != null)
{
currentRow.AddressLogId = a; ////<--Update Me. *Special Case. Notice: Not the same as field to match
}
sysHeader.Clear();
importedValues.Clear();
}
//----------------------------------------Run Object Entries with Create or Update ------------------------------------//
if (currentRow.CustomerId == null)
{
new CustomerRepository().Create(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = currentRow
});
response.Inserted = response.Inserted + 1;
}
else
{
new CustomerRepository().Update(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = currentRow,
EntityId = currentRow.CustomerId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
myErrors.Add(myImpHelp.eMessage3(row, ex.Message));
}
}
return(response);
}
public CustomerRowChangeEvent(CustomerRow row, global::System.Data.DataRowAction action) {
this.eventRow = row;
this.eventAction = action;
}
public void AddCustomerRow(CustomerRow row)
{
this.Rows.Add(row);
}
public CustomerRowChangeEvent(CustomerRow row, global::System.Data.DataRowAction action)
{
this.eventRow = row;
this.eventAction = action;
}
public AccountRow AddAccountRow(CustomerRow parentCustomerRowByCustomerAccount, int AccountType, decimal SavingsBalance, decimal Checking_Balance) {
AccountRow rowAccountRow = ((AccountRow)(this.NewRow()));
rowAccountRow.ItemArray = new object[] {
null,
parentCustomerRowByCustomerAccount[0],
AccountType,
SavingsBalance,
Checking_Balance};
this.Rows.Add(rowAccountRow);
return rowAccountRow;
}
