MSCRM2011: ExcelUploadUsing Javascripts

--><head>
<meta charset="utf-8">

<title></title>
<link href="../styles/styles.css" rel="stylesheet" type="text/css">
<script src="../Scripts/JQuery1.4.4.min.js" type="text/javascript"></script>
<script src="../Scripts/JQueryLibrary.js" type="text/javascript"></script>

<script src="../Scripts/json2.js" type="text/javascript"></script>
<script src="../Scripts/XrmServiceToolkitSOAP.js" type="text/javascript"></script>
<script type="text/javascript">
function ExcelUpload() {
debugger;

if (window.parent.Xrm.Page.data.entity.getId() != null) {
var guId = window.parent.Xrm.Page.data.entity.getId();

var FileName = document.getElementById("Browse").value;

var validExts = new Array(".xlsx", ".xls");
var fileExt = FileName;
fileExt = fileExt.substring(fileExt.lastIndexOf('.'));
if (fileExt == ".xlsx" || fileExt == ".xls") {
var fullPath = FileName;
var FileType;
var startIndex = (fullPath.indexOf('\\') >= 0 ? fullPath.lastIndexOf('\\') : fullPath.lastIndexOf('/'));
var filename = fullPath.substring(startIndex);
if (filename.indexOf('\\') === 0 || filename.indexOf('/') === 0) {
filename = filename.substring(1);

FileType = filename.substr(0, filename.lastIndexOf('.')) || input;
}

var fetch = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
"<entity name='hppscm_collectionupload'>" +
"<attribute name='hppscm_collectionuploadid' />" +
"<attribute name='hppscm_name' />" +
"<attribute name='hppscm_mode_of_payment' />" +
"<attribute name='hppscm_icr_reference_number' />" +
"<attribute name='hppscm_ddnumber' />" +
"<attribute name='hppscm_dddate' />" +
"<attribute name='hppscm_ddamount' />" +
"<attribute name='hppscm_chequesalename' />" +
"<attribute name='hppscm_bankreconciliation' />" +
"<attribute name='hppscm_bankname' />" +
"<order attribute='hppscm_name' descending='false' />" +
"<filter type='and'>" +
"<condition attribute='hppscm_name' operator='eq' value='hppscm_importdata' />" +
"</filter>" +
"</entity>" +
"</fetch>";
var bankreconciliationFileName;
var chequesalenameFileName;
var modepayment; var icrreferencenumber; var dnumber; var ddate; var damount; var bname;
var collectionfiles = XrmServiceToolkit.Soap.Fetch(fetch);
if (collectionfiles.length > 0) {

if (collectionfiles[0].attributes["hppscm_bankreconciliation"] != undefined && collectionfiles[0].attributes.hppscm_bankreconciliation.value != null) {
bankreconciliationFileName = collectionfiles[0].attributes.hppscm_bankreconciliation.value;
// chequesalenameFileName = collectionfiles[0].attributes.hppscm_chequesalename.value;
modepayment = collectionfiles[0].attributes.hppscm_mode_of_payment.value;
icrreferencenumber = collectionfiles[0].attributes.hppscm_icr_reference_number.value;
bname = collectionfiles[0].attributes.hppscm_bankname.value;
ddate = collectionfiles[0].attributes.hppscm_dddate.value;
damount = collectionfiles[0].attributes.hppscm_ddamount.value;
dnumber = collectionfiles[0].attributes.hppscm_ddnumber.value;
}
}

if (FileType.toLowerCase() == bankreconciliationFileName.toLowerCase()) {

var excel = new ActiveXObject("Excel.Application");
var excel_file = excel.Workbooks.Open(FileName);
var excel_sheet = excel_file.Worksheets("Sheet1");
var Rows = excel_sheet.UsedRange.Cells.Rows.Count;
var Columns = excel_sheet.UsedRange.Cells.Columns.Count;
var PaymentMode = excel_file.ActiveSheet.Cells(1, 1).Value;
var date = excel_file.ActiveSheet.Cells(1, 2).Value;
var Number = excel_file.ActiveSheet.Cells(1, 3).Value;
var Name = excel_file.ActiveSheet.Cells(1, 4).Value;
var ICRefno = excel_file.ActiveSheet.Cells(1, 5).Value;
var Amount = excel_file.ActiveSheet.Cells(1, 6).Value;
if (PaymentMode.toLowerCase() == modepayment.toLowerCase() && date.toLowerCase() == ddate.toLowerCase() && Number.toLowerCase() == dnumber.toLowerCase() && Name.toLowerCase() == bname.toLowerCase() && ICRefno.toLowerCase() == icrreferencenumber.toLowerCase() && Amount.toLowerCase() == damount.toLowerCase() && Columns == 6) {

var GrandTotal = 0;
var sum = 0;
for (var i = 1; i < Rows ; i++) {

try {

var Paymode = new XrmServiceToolkit.Soap.BusinessEntity("hppscm_paymentmodeselection");

if (excel_file.ActiveSheet.Cells(i + 1, 1).Value != undefined) {
var modeofpayment = excel_file.ActiveSheet.Cells(i + 1, 1).Value;
Paymode.attributes["hppscm_name"] = modeofpayment;

}
if (excel_file.ActiveSheet.Cells(i + 1, 2).Value != undefined) {

var Ddate = new Date(excel_file.ActiveSheet.Cells(i + 1, 2).Value);
Paymode.attributes["hppscm_dddate"] = Ddate;
}

if (excel_file.ActiveSheet.Cells(i + 1, 3).Value != undefined) {
var DDNumber = String(excel_file.ActiveSheet.Cells(i + 1, 3).Value);
Paymode.attributes["hppscm_transactionnumber"] = DDNumber;
}
if (excel_file.ActiveSheet.Cells(i + 1, 4).Value != undefined) {
var BankName = excel_file.ActiveSheet.Cells(i + 1, 4).Value;
Paymode.attributes["hppscm_bankname"] = BankName;

}
if (excel_file.ActiveSheet.Cells(i + 1, 5).Value != undefined) {
var ICRRefno = excel_file.ActiveSheet.Cells(i + 1, 5).Value;
Paymode.attributes["hppscm_referencenumber"] = ICRRefno;
}
if (excel_file.ActiveSheet.Cells(i + 1, 6).Value != undefined) {
var DDAmount = excel_file.ActiveSheet.Cells(i + 1, 6).Value;
Paymode.attributes["hppscm_fees"] = { value: DDAmount, type: "Money" };
sum = sum + DDAmount;

}

Paymode.attributes["hppscm_paymentmodeid"] = { id: guId, logicalName: "hppscm_paymentmodeselection", type: "EntityReference" };
Paymode.attributes["hppscm_bankreconcilation"] = { value: 1, type: "OptionSetValue" };
Paymode.attributes["hppscm_transactionidentifier"] = { value: 2, type: "OptionSetValue" };

var fetchXML = "<fetch version='1.0' output-format='xml-platform' mapping='logical' >" +

"<entity name='hppscm_modeofpayment'>" +

"<attribute name='hppscm_modeofpaymentid' />" +

"<attribute name='hppscm_name' />" +

"<filter type='and'>" +

"<condition attribute='hppscm_name' operator='eq' value='" + modeofpayment + "' />" +

"</filter>" +

"</entity>" + "</fetch>";

var PaymentMode = XrmServiceToolkit.Soap.Fetch(fetchXML);

if (PaymentMode.length > 0) {
var paymentid = PaymentMode[0].attributes.hppscm_modeofpaymentid.value;
Paymode.attributes["hppscm_paymentmodes"] = { id: paymentid, logicalName: "hppscm_paymentmodeselection", type: "EntityReference" };
XrmServiceToolkit.Soap.Create(Paymode);

}
else {
alert("Mode of Payment doesnot available in paymentmode masters");
XrmServiceToolkit.Soap.CreateErrorLog("RowNumber: " + i, "Mode of Payment doesnot available in paymentmode masters ","Check Payment Masters");
break;
}

}
catch (error) {
XrmServiceToolkit.Soap.CreateErrorLog("BankReconciliation", error.message, error.stack + "\r\n");
}
}

if (FileType.toLowerCase() == bankreconciliationFileName.toLowerCase() && PaymentMode.length > 0) {

alert("Bank Reconciliation file successfully uploaded.");

var webResource = window.parent.Xrm.Page.ui.controls.get("WebResource_RICustomHTMLSubgridPaymentSelection");
webResource.setSrc(webResource.getSrc());
var webResource =window.parent.Xrm.Page.ui.controls.get("WebResource_PaymentmodeGrandtotals");
webResource.setSrc(webResource.getSrc());
if (window.parent.Xrm.Page.getAttribute("hppscm_total_payments_fee").getValue() != null) {

var payments = window.parent.Xrm.Page.getAttribute("hppscm_total_payments_fee").getValue()
GrandTotal = parseFloat(payments) + parseFloat(sum);
window.parent.Xrm.Page.getAttribute("hppscm_total_payments_fee").setValue(GrandTotal.toString());
window.parent.Xrm.Page.getAttribute("hppscm_total_payments_fee").setSubmitMode("always");
window.parent.Xrm.Page.data.entity.save();
}

XrmServiceToolkit.Soap.CreateErrorLog("RowNumber: " + i, "Bank Reconciliation file Successfully Uploaded ","Collection data");

}

}

else {
alert("Invaild Bank Reconciliation excel file format.");

}

}
else {
alert(" Invalid Bank Reconciliation file Name format.");
}

}
else {

alert("Please select a Bank Reconciliation Excel file to upload.");
}

}

}

</script>
</head>
<body>

MSCRM2011

Tuesday, 17 February 2015

ExcelUploadUsing Javascripts

No comments:

Post a Comment