using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Windows;
using
Excel = Microsoft.Office.Interop.Excel;
using
System.Text;
using
System.Configuration;
using
Applications.Common.Business;
using
System.ServiceModel.Description;
using
Microsoft.Xrm.Sdk.Client;
using
Microsoft.Xrm.Sdk.Query;
using
Microsoft.Xrm.Sdk;
using
System.Windows.Input;
namespace
PopulateFCNs
{
public struct ItemToPost
{
public string EntityGuid;
public string FCN;
public string CorpCode;
public string DocumentType;
public string Combined()
{
return String.Format("{0}.{1}.{2}.{3}", EntityGuid, FCN, CorpCode,
DocumentType);
}
}
/// <summary>
/// Interaction logic for
MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
List<document> _documents;
Dictionary<string, IOrganizationService> _orgs;
Dictionary<string, ItemToPost> _billItems = new Dictionary<string, ItemToPost>();
Dictionary<string, ItemToPost> _statementItems = new Dictionary<string, ItemToPost>();
public MainWindow()
{
InitializeComponent();
_orgs = new Dictionary<string, IOrganizationService>();
}
private void buttonRead_Click(object sender, RoutedEventArgs e)
{
const string fileName = @"C:\Projects\Sandbox
- James Cooke\PopulateFCNs\Data\book1.xlsx";
_documents = new List<document>();
Excel.Workbook myBook = null;
Excel.Application myApp = null;
Excel.Worksheet mySheet = null;
int lastRow = 0;
myApp = new Excel.Application();
myApp.Visible = true;
myBook =
myApp.Workbooks.Open(fileName);
mySheet
= (Excel.Worksheet)myBook.Sheets[1]; // Explicit cast is not
required here
lastRow
= mySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
for (int index = 1; index <=
lastRow; index++)
{
Array cells = (Array)mySheet.get_Range("A" + index, "H" + index).Cells.Value;
document doc = new document();
doc.CorpCode = getXlsData(cells, 3);
if (doc.CorpCode.Length != 3)
continue;
doc.DocumentType = getXlsData(cells, 4);
if (doc.DocumentType != "Statement" && doc.DocumentType
!= "Bill")
continue;
doc.FacilityControlNumbers = getXlsData(cells, 6);
doc.InvoiceNumber = getXlsData(cells, 7);
doc.StatementNumber = getXlsData(cells, 8);
_documents.Add(doc);
labelCounter.Content = index.ToString();
}
myBook.Close();
myApp.Quit();
ShowResults(_documents);
}
private void ShowResults(List<document> _documents)
{
StringBuilder sb = new StringBuilder();
txt.Clear();
foreach (var document in _documents)
{
sb.Append(String.Format("{0}\t{1}\t{2}\t{3}{4}", document.CorpCode,
document.DocumentType, (document.DocumentType == "Bill" ? document.InvoiceNumber : document.StatementNumber),
document.FacilityControlNumbers, Environment.NewLine));
}
txt.Text
= sb.ToString();
}
public string getXlsData(Array cells, int columnNumber )
{
if
(cells.GetValue(1,columnNumber)!=null)
{
return cells.GetValue(1,
columnNumber).ToString();
}
else
{
return string.Empty;
}
}
private void ButtonCalc_Click(object sender, RoutedEventArgs e)
{
int counter = 0;
_billItems.Clear();
Mouse.OverrideCursor = Cursors.Wait;
try
{
string fetchXml;
foreach (document doc in _documents)
{
IOrganizationService org =
getXrmService(doc.CorpCode);
switch (doc.DocumentType)
{
case "Bill":
fetchXml = GetBillFetchXml(doc.InvoiceNumber);
EntityCollection bills =
org.RetrieveMultiple(new FetchExpression(fetchXml));
if (bills.Entities.Count>0)
{
foreach (string fcn in
doc.FacilityControlNumbers.Split(','))
{
string billGuid =
bills[0].Attributes["fcbt_billid"].ToString();
ItemToPost itp = new ItemToPost() { EntityGuid = billGuid,
FCN = fcn,CorpCode=doc.CorpCode,DocumentType=doc.DocumentType };
if
(_billItems.ContainsKey(itp.Combined()))
continue;
_billItems.Add(itp.Combined(),itp);
txt_data.Text += String.Format("{0}\t{1}\t{2}\t{3}{4}", doc.CorpCode,
doc.DocumentType, billGuid, fcn, Environment.NewLine);
}
}
break;
case "Statement":
fetchXml = GetStatementFetchXml(doc.StatementNumber);
EntityCollection statements =
org.RetrieveMultiple(new FetchExpression(fetchXml));
if (statements.Entities.Count
> 0)
{
foreach (string fcn in
doc.FacilityControlNumbers.Split(','))
{
string statementGuid =
statements[0].Attributes["fcbt_statementid"].ToString();
ItemToPost itp = new ItemToPost() { EntityGuid =
statementGuid, FCN = fcn, CorpCode = doc.CorpCode, DocumentType =
doc.DocumentType };
if (_statementItems.ContainsKey(itp.Combined()))
continue;
_statementItems.Add(itp.Combined(), itp);
txt_data.Text += String.Format("{0}\t{1}\t{2}\t{3}{4}", doc.CorpCode, doc.DocumentType,
statementGuid, fcn, Environment.NewLine);
}
}
break;
default:
throw new Exception("Invalid doc type in
xml:" +
doc.DocumentType);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
Mouse.OverrideCursor = null;
}
}
private string GetBillFetchXml(string invoiceNumber)
{
string data = string.Empty;
data += @"<fetch
version='1.0' output-format='xml-platform' mapping='logical'
distinct='false'>" +
Environment.NewLine;
data += @" <entity
name='fcbt_bill'>" +
Environment.NewLine;
data += @" <attribute name='fcbt_billid'
/>" + Environment.NewLine;
data += @" <filter type='and'>" + Environment.NewLine;
data += @" <condition
attribute='fcbt_invoicenumber' operator='eq' value='invnum' />" + Environment.NewLine;
data += @" </filter>" + Environment.NewLine;
data += @" </entity>" + Environment.NewLine;
data += @" </fetch>" + Environment.NewLine;
return data.Replace("invnum", invoiceNumber);
}
private string GetStatementFetchXml(string statementNumber)
{
string data = string.Empty;
data += @"<fetch
version='1.0' output-format='xml-platform' mapping='logical'
distinct='false'>" +
Environment.NewLine;
data += @" <entity
name='fcbt_statement'>" +
Environment.NewLine;
data += @" <attribute
name='fcbt_statementid' />" + Environment.NewLine;
data += @" <filter type='and'>" + Environment.NewLine;
data += @" <condition
attribute='fcbt_statementnumber' operator='eq' value='stmtnum' />" + Environment.NewLine;
data += @" </filter>" + Environment.NewLine;
data += @" </entity>" + Environment.NewLine;
data += @" </fetch>" + Environment.NewLine;
return data.Replace("stmtnum", statementNumber);
}
IOrganizationService getXrmService(string corpCode)
{
if(_orgs.ContainsKey(corpCode))
{
return _orgs[corpCode];
}
else
{
string url=ConfigurationManager.AppSettings["xrm_"+corpCode];
if(string.IsNullOrEmpty(url))
{
throw new Exception("app.config is missing
service endpoint for corpcode=" + corpCode);
}
else
{
IOrganizationService org = CreateXRMService(url);
_orgs.Add(corpCode, org);
return org;
}
}
}
private IOrganizationService CreateXRMService(string url)
{
// Get credentials from
credential database.
string domain;
string userName;
string password;
string environmentName = ConfigurationManager.AppSettings["RobotSecurityEnvironmentXrm"];
Uri organizationUri = new Uri(url);
RobotSecurity.GetCredentialsForEnvironment(environmentName,
out domain, out userName, out password);
userName
= domain + @"\" + userName; // XRM is happier if you do this
ClientCredentials Credentials = new ClientCredentials();
Credentials.UserName.UserName = userName;
Credentials.UserName.Password = password;
//Credentials.Windows.ClientCredential
= new System.Net.NetworkCredential(userName, password, domain);
// INITIALIZE PIPELINE
SERVICE
OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(organizationUri, null, Credentials, null);
serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());
IOrganizationService service = (IOrganizationService)serviceProxy;
// Returns the service
return service;
}
private void ButtonPost_Click(object sender, RoutedEventArgs e)
{
try
{
foreach (KeyValuePair<string, ItemToPost> item in _billItems)
{
IOrganizationService svc =
getXrmService(item.Value.CorpCode);
Entity
parentBillEntity = svc.Retrieve("fcbt_bill", new Guid(item.Value.EntityGuid), new ColumnSet(false));
Entity billFacilityEntity = new Entity("fcbt_billfacility");
billFacilityEntity.Attributes.Add("fcbt_bill", parentBillEntity.ToEntityReference());//foreign key
billFacilityEntity.Attributes.Add("fcbt_facilitycontrolnumber", item.Value.FCN);
svc.Create(billFacilityEntity);
}
foreach (KeyValuePair<string, ItemToPost> item in _statementItems)
{
IOrganizationService svc =
getXrmService(item.Value.CorpCode);
Entity parentStatementEntity =
svc.Retrieve("fcbt_statement", new Guid(item.Value.EntityGuid), new ColumnSet(false));
Entity statementFacilityEntity = new Entity("fcbt_statementfacility");
statementFacilityEntity.Attributes.Add("fcbt_statement",
parentStatementEntity.ToEntityReference());//foreign key
statementFacilityEntity.Attributes.Add("fcbt_facilitycontrolnumber", item.Value.FCN);
svc.Create(statementFacilityEntity);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
