using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using GZTW.AyaNova.BLL;
using System.Threading;
using CSLA.Security;
using FileHelpers;
using System.Collections.Generic;
using System.IO;
namespace AyaNova.PlugIn.ImportExportCSV
{
///
/// Summary description for Form1.
///
public class ImportRate : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGridView DataGridView1;
private System.Windows.Forms.MainMenu mainMenu1;
private System.Windows.Forms.MenuItem mnuOpen;
private System.Windows.Forms.MenuItem mnuImport;
private System.Windows.Forms.OpenFileDialog dlgOpen;
private System.Windows.Forms.MenuItem mnuExport;
private MenuItem mnuImportOptions;
private IContainer components;
public ImportRate()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
this.Icon = Resource.ImportExportCSV16icon;
}
///
/// Clean up any resources being used.
///
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
this.components = new System.ComponentModel.Container();
this.DataGridView1 = new System.Windows.Forms.DataGridView();
this.mainMenu1 = new System.Windows.Forms.MainMenu(this.components);
this.mnuOpen = new System.Windows.Forms.MenuItem();
this.mnuImport = new System.Windows.Forms.MenuItem();
this.mnuExport = new System.Windows.Forms.MenuItem();
this.mnuImportOptions = new System.Windows.Forms.MenuItem();
this.dlgOpen = new System.Windows.Forms.OpenFileDialog();
((System.ComponentModel.ISupportInitialize)(this.DataGridView1)).BeginInit();
this.SuspendLayout();
//
// DataGridView1
//
this.DataGridView1.Dock = System.Windows.Forms.DockStyle.Fill;
this.DataGridView1.Location = new System.Drawing.Point(0, 0);
this.DataGridView1.Name = "DataGridView1";
this.DataGridView1.ReadOnly = true;
this.DataGridView1.Size = new System.Drawing.Size(632, 433);
this.DataGridView1.TabIndex = 1;
this.DataGridView1.DataBindingComplete += new System.Windows.Forms.DataGridViewBindingCompleteEventHandler(this.DataGridView1_DataBindingComplete);
this.DataGridView1.BindingContextChanged += new System.EventHandler(this.DataGridView1_BindingContextChanged);
//
// mainMenu1
//
this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
this.mnuOpen,
this.mnuImport,
this.mnuExport,
this.mnuImportOptions});
//
// mnuOpen
//
this.mnuOpen.Index = 0;
this.mnuOpen.Text = "&Open CSV file";
this.mnuOpen.Click += new System.EventHandler(this.mnuOpen_Click);
//
// mnuImport
//
this.mnuImport.Enabled = false;
this.mnuImport.Index = 1;
this.mnuImport.Text = "&Import data";
this.mnuImport.Click += new System.EventHandler(this.mnuImport_Click);
//
// mnuExport
//
this.mnuExport.Index = 2;
this.mnuExport.Text = "&Export data";
this.mnuExport.Click += new System.EventHandler(this.mnuExport_Click);
//
// mnuImportOptions
//
this.mnuImportOptions.Index = 3;
this.mnuImportOptions.Text = "&Duplicate import options";
this.mnuImportOptions.Visible = false;
this.mnuImportOptions.Click += new System.EventHandler(this.mnuImportOptions_Click);
//
// dlgOpen
//
this.dlgOpen.DefaultExt = "csv";
this.dlgOpen.Filter = "CSV files|*.csv";
this.dlgOpen.Title = "Comma separated values Rate file";
//
// ImportRate
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 15);
this.ClientSize = new System.Drawing.Size(632, 433);
this.Controls.Add(this.DataGridView1);
this.Menu = this.mainMenu1;
this.Name = "ImportRate";
this.Text = "Rate importer v2.0.0.0";
this.WindowState = System.Windows.Forms.FormWindowState.Maximized;
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.DataGridView1)).EndInit();
this.ResumeLayout(false);
}
#endregion
//-----------------------------------------------------------------------------
//
// _________ __
// \_ ___ \ __ __ _______/ |_ ____ _____
// / \ \/ | | \ / ___/\ __\ / _ \ / \
// \ \____| | / \___ \ | | ( <_> )| Y Y \
// \______ /|____/ /____ > |__| \____/ |__|_| /
// \/ \/ \/
// _________ .___
// \_ ___ \ ____ __| _/ ____
// / \ \/ / _ \ / __ | _/ __ \
// \ \____( <_> )/ /_/ | \ ___/
// \______ / \____/ \____ | \___ >
// \/ \/ \/
#region list objects
//These are name value lists used to
//keep track of the id's of existing items
//that are either pre-existing or have been created during import
//this supports the code that matches text names of objects to their id's
//i.e. "IBM" as a manufacturer to the IBM record in AyaNova already
//case is not taken into account during comparison to ensure no little
//mismatches due to a small typo in the import file
#endregion
#region Form load and login
private void Form1_Load(object sender, System.EventArgs e)
{
{
System.Reflection.Assembly a = System.Reflection.Assembly.GetExecutingAssembly();
string sVersion = "AyaNovaź Rate import / export " + AyaBizUtils.DisplayVersion(a.GetName().Version);
System.Diagnostics.FileVersionInfo fileVersion = System.Diagnostics.FileVersionInfo.GetVersionInfo(a.Location);
if (fileVersion.FileBuildPart > 0)
sVersion += " (Patch " + fileVersion.FileBuildPart.ToString() + ")";
this.Text = sVersion;
}
mnuImportOptions.Visible = Util.LicensedMode;
MessageBox.Show(
"Confirm that you have a backup copy of your AyaNova database before proceeding.\r\n\r\n" +
"There is no undo function for importing data other than restoring from a pre-import backup copy.\r\n\r\n" +
"** If you are not 100% certain that you have a *working* backup copy ready, stop now **",
"ACCIDENTS HAPPEN: DO NOT IMPORT WITHOUT A GOOD BACKUP!");
}
#endregion
#region Read import file
private DataTable dtRates;
//private FHRate[] records;
private void mnuOpen_Click(object sender, System.EventArgs e)
{
if (dlgOpen.ShowDialog() != DialogResult.OK) return;
this.mnuImport.Enabled = false;
Util.ReadCSV(DataGridView1, ref dtRates, RootObjectTypes.Rate, dlgOpen.FileName);
this.mnuImport.Enabled = true;
}
#endregion
#region Import data
Dictionary lsMap = null;
private void mnuImport_Click(object sender, System.EventArgs e)
{
//Save grid settings
Util.SaveGridMappings(DataGridView1, RootObjectTypes.Rate);
if (dtRates.Rows.Count < 1)
{
MessageBox.Show("There are no records to import");
return;
}
//Get the map
lsMap = Util.BuildGridImportMap(DataGridView1);
if (!lsMap.ContainsKey("Name"))
{
MessageBox.Show("\"Name\" is a required field to uniquely identify Rates. Select a column to be used as the unique Rate name to proceed with import.");
return;
}
if (!lsMap.ContainsKey("RateType"))
{
MessageBox.Show("\"RateType\" wasn't specified. Import will default to Service type rates.");
}
long lBadCount = 0;
long lGoodCount = 0;
long lUpdatedCount = 0;
//case 1737 cache
ClientGroups cgroups = ClientGroups.GetItems();
Dictionary regions = new Dictionary();
RateUnitChargeDescriptions rateUnitChageDescriptions = RateUnitChargeDescriptions.GetItems();
RegionList rl = RegionList.GetList("");
foreach (RegionList.RegionListInfo i in rl)
{
//case 3606
if (!regions.ContainsKey(i.LT_Region_Label_Name.Display))
regions.Add(i.LT_Region_Label_Name.Display, i.LT_Region_Label_Name.Value);
}
//Unique to rates is that they are an editable child collection
//so need to fetch it all and use it in this function
Rates TheRates = Rates.GetItems(false);
Waiting w = new Waiting();
w.Show();
w.Ops = "Importing Rates";
foreach (DataRow dr in dtRates.Rows)
{
if (fld(dr, "Name", 255) == "") continue;
Rate c = null;
string sRateName = fld(dr, "Name", 255);
if (TheRates.Contains(sRateName))
{
//case 1404 Open existing for update?
if (Util.LicensedMode && Util.IEData.RateUpdate)
{
//update existing Rate
c = TheRates[sRateName];
}
else//not licensed or nothing chosen to update
{
lBadCount++;
continue;
}
}
else
{
c = TheRates.Add();
}
w.Step = sRateName;
if (c.IsNew)
c.Name = sRateName;
if (c.IsNew || Util.IEData.RateDescription)
{
c.Description = fld(dr, "Description", 255);
}
if (c.IsNew || Util.IEData.RateAccountNumber)
{
c.AccountNumber = fld(dr, "AccountNumber", 255);
}
if (c.IsNew || Util.IEData.RateCost)
{
c.Cost = StringToDecimal(fld(dr, "Cost", 255));
}
if (c.IsNew || Util.IEData.RateCharge)
{
c.Charge = StringToDecimal(fld(dr, "RetailCharge", 255));
}
if (c.IsNew || Util.IEData.RateContractRate)
c.ContractRate = StringToBool(fld(dr, "ContractRate", 255));
if (c.IsNew || Util.IEData.RateType)
c.RateType = GetRateTypeByName(fld(dr, "RateType", 255));
//------------------------------------------------
//Rate group, region, dispatch zone
//rate unit charge description
if (c.IsNew || Util.IEData.RateUnitChargeDescription)
{
Guid RateUnitChargeDescriptionID = Guid.Empty;
string s = fld(dr, "UnitChargeDescription", 255).Trim();
if (!string.IsNullOrWhiteSpace(s))
{
foreach (RateUnitChargeDescription ruc in rateUnitChageDescriptions)
{
if (ruc.Name.Equals(s, StringComparison.CurrentCultureIgnoreCase))
{
RateUnitChargeDescriptionID = ruc.ID;
break;
}
}
if (RateUnitChargeDescriptionID == Guid.Empty)
{
//wasn't in the list so add it and save it
RateUnitChargeDescription ruc = rateUnitChageDescriptions.Add();
ruc.Name = s;
RateUnitChargeDescriptionID = ruc.ID;
rateUnitChageDescriptions = (RateUnitChargeDescriptions)rateUnitChageDescriptions.Save();
}
}
c.RateUnitChargeDescriptionID = RateUnitChargeDescriptionID;
}
//client group
if (c.IsNew || Util.IEData.ClientGroup)
{
Guid ClientGroupID = Guid.Empty;
string s = fld(dr, "ClientGroup", 255).Trim();
if (!string.IsNullOrWhiteSpace(s))
{
foreach (ClientGroup cg in cgroups)
{
if (cg.Name.Equals(s, StringComparison.CurrentCultureIgnoreCase))
{
ClientGroupID = cg.ID;
break;
}
}
if (ClientGroupID == Guid.Empty)
{
//wasn't in the list so add it and save it
ClientGroup cg = cgroups.Add();
cg.Name = s;
ClientGroupID = cg.ID;
cgroups = (ClientGroups)cgroups.Save();
}
}
c.ClientGroupID = ClientGroupID;
}
//region
if (c.IsNew || Util.IEData.RateRegion)
{
Guid RegionID = GZTW.AyaNova.BLL.Region.DefaultRegionID;
string s = fld(dr, "Region", 255).Trim();
if (!string.IsNullOrWhiteSpace(s))
{
if (regions.ContainsKey(s))
RegionID = regions[s];
else
{
GZTW.AyaNova.BLL.Region r = GZTW.AyaNova.BLL.Region.NewItem();
r.Active = true;
r.Name = s;
RegionID = r.ID;
r = (GZTW.AyaNova.BLL.Region)r.Save();
regions.Add(r.Name, r.ID);
}
}
c.RegionID = RegionID;
}
//================================================
//modified from normal single root object save
//saves (and updates) the collection on each item
if (TheRates.IsSavable)
{
bool bWasNew = c.IsNew;
TheRates = (Rates)TheRates.Save();
if (bWasNew)
lGoodCount++;
else
lUpdatedCount++;
}
else
{
lBadCount++;
}
}
w.Close();
if (Util.LicensedMode && Util.IEData.RateUpdate)
{
if (lBadCount > 0)
MessageBox.Show(lGoodCount.ToString() + " Rates were imported / " + lUpdatedCount + " duplicate Rates were updated sucessfully.\r\n" +
lBadCount.ToString() + " Rates were not imported because they had no name at all or did not pass the business / update rules for a Rate object.");
else
MessageBox.Show(lGoodCount.ToString() + " Rates were imported / " + lUpdatedCount + " duplicate Rates were updated sucessfully.");
}
else
{
if (lBadCount > 0)
MessageBox.Show(lGoodCount.ToString() + " Rates were imported sucessfully.\r\n" +
lBadCount.ToString() + " Rates were not imported because a Rate with the same name already\r\n" +
"exists or they had no name at all or did not pass the rules for a Rate object.");
else
MessageBox.Show(lGoodCount.ToString() + " Rates were imported sucessfully.");
}
}
///
/// Get a field from the current data row
///
///
///
///
private string fld(DataRow dr, string sColumnNameKey, int nLength)
{
if (lsMap.ContainsKey(sColumnNameKey))
{
return t(nLength, dr[lsMap[sColumnNameKey]].ToString());
}
else
return "";
}
///
///
///
///
///
///
private static string t(int nLength, string s)
{
if (string.IsNullOrWhiteSpace(s)) return "";
if (s.Length <= nLength) return s;
else
return s.Substring(0, nLength);
}
private decimal StringToDecimal(string s)
{
if (s == null || s == "") return 0M;
try
{
return System.Convert.ToDecimal(s);
}
catch
{
return 0M;
}
}
private bool StringToBool(string s)
{
if (s == null || s == "") return false;
if (s.ToUpper().StartsWith("T")) return true;
if (s == "1") return true;
return false;
}
//get rate type by name, if it doesn't start with T then it's a service rate
private RateTypes GetRateTypeByName(string sName)
{
if (sName == "") return RateTypes.Service;
sName = sName.ToUpper();
if (sName.StartsWith("T"))
return RateTypes.Travel;
return RateTypes.Service;
}
#endregion
#region Export data
///
/// Exports all existing Rates to CSV format identical
/// to that required for import
///
///
///
private void mnuExport_Click(object sender, System.EventArgs e)
{
#region Cache lists
ClientGroups cgroups = ClientGroups.GetItems();
RateUnitChargeDescriptions rateUnitChageDescriptions = RateUnitChargeDescriptions.GetItems();
RegionList regions = RegionList.GetList("");
#endregion Cached lists
SaveFileDialog sf = new SaveFileDialog();
sf.DefaultExt = ".csv";
sf.FileName = "Rates.csv";
if (sf.ShowDialog() != DialogResult.OK) return;
Cursor = Cursors.WaitCursor;
FileHelperEngine engine = new FileHelperEngine(typeof(FHRate));
System.Collections.ArrayList alRates = new ArrayList();
FHRate fhc;
//Rate c;
Rates TheRates = Rates.GetItems(false);
Waiting w = new Waiting();
w.Show();
w.Ops = "Exporting Rates";
foreach (Rate i in TheRates)
{
fhc = new FHRate();
w.Step = i.Name;
fhc.Name = i.Name;
fhc.Description = i.Description;
fhc.AccountNumber = i.AccountNumber;
fhc.Cost = i.Cost.ToString();
fhc.RetailCharge = i.Charge.ToString();
fhc.ContractRate = (i.ContractRate ? "TRUE" : "FALSE");
fhc.RateType = i.RateType.ToString().ToUpperInvariant();
if (i.RateUnitChargeDescriptionID == Guid.Empty)
{
fhc.UnitChargeDescription = "";
}
else
{
if (rateUnitChageDescriptions.Contains(i.RateUnitChargeDescriptionID))
{
fhc.UnitChargeDescription = rateUnitChageDescriptions[i.RateUnitChargeDescriptionID].Name;
}
else
{
fhc.UnitChargeDescription = "";
}
}
//Client group
if (i.ClientGroupID == Guid.Empty)
{
fhc.ClientGroup = "";
}
else
{
if (cgroups.Contains(i.ClientGroupID))
{
fhc.ClientGroup = cgroups[i.ClientGroupID].Name;
}
else
{
fhc.ClientGroup = "";
}
}
//Region
if (i.RegionID == Guid.Empty)
{
fhc.Region = "";
}
else
{
if (regions.Contains(i.RegionID))
{
fhc.Region = regions[i.RegionID];
}
else
{
fhc.Region = "";
}
}
alRates.Add(fhc);
}
w.Close();
try
{
engine.WriteFile(sf.FileName, (object[])alRates.ToArray(typeof(FHRate)));
}
catch (Exception ex)
{
MessageBox.Show("Error writing the export file:\r\n\r\n" + ex.Message);
return;
}
Cursor = Cursors.Default;
MessageBox.Show("All Rates in current AyaNova database have been exported to file:\r\n" +
sf.FileName);
}
#endregion
private void mnuImportOptions_Click(object sender, EventArgs e)
{
Util.ShowOptionsDialog();
}
#region case 1588 user selectable column placement
private void DataGridView1_BindingContextChanged(object sender, EventArgs e)
{
// Continue only if the data source has been set.
if (DataGridView1.DataSource == null)
{
return;
}
//these are the columns that can be selected by the user to import into
List ls = new List();
ls.Add("Name");
ls.Add("Description");
ls.Add("AccountNumber");
ls.Add("Cost");
ls.Add("RetailCharge");
ls.Add("ContractRate");
ls.Add("RateType");
ls.Add("UnitChargeDescription");
ls.Add("ClientGroup");
ls.Add("Region");
// Add the AutoFilter header cell to each column.
foreach (DataGridViewColumn col in DataGridView1.Columns)
{
col.HeaderCell = new
GZDataGridViewComboColumnHeaderCell(col.HeaderCell, ls);
}
// Resize the columns to fit their contents.
DataGridView1.AutoResizeColumns();
}
private void DataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{
}
#endregion case 1588
//------------------------------------------------------------------------
}
}