admin/ayanova7
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
ayanova7/utils/AyaImport/Form1.cs
John Cardinal c0e77aa731
2023-07-14 21:26:08 +00:00

7559 lines
384 KiB
C#
Raw Permalink Blame History

#define ATTEMPT_UPGRADE_FROM_142
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Threading;
using System.Data.OleDb;
using System.Text;
using CSLA.Security;
using GZTW.AyaNova.BLL;
using CSLA;
namespace AyaImport
{
/// <summary>
/// Summary description for ImportForm.
/// </summary>
public class ImportForm : System.Windows.Forms.Form
{
#region Attributes
private System.Windows.Forms.Label label1;
private System.Windows.Forms.ComboBox cbSource;
//private System.ComponentModel.IContainer components;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
//An array to hold AyaNovaDBItem objects (containing path info) to bind to the combo box
private ArrayList ComboItems;
private System.Windows.Forms.TextBox edStatus;
private System.Windows.Forms.Button btnStopSearch;
private bool bFilling = false;
private bool bContinue = true;
private string sOut1 = "";
private string sOut2 = "";
private string sOut3 = "";
private string sOut4 = "";
private string sOut5 = "";
private System.Windows.Forms.Button btnImport;
private System.Windows.Forms.OpenFileDialog openFileDialog1;
long lFoundCount = 0;
private System.Windows.Forms.ProgressBar pBar;
private System.Windows.Forms.Label lblCurrentOp;
private string strOldDBPath = "";
enum OpState
{
Pending,
Calculating,
Canceled,
}
OpState _state = OpState.Pending;
#endregion
#region constructor / dispose
public ImportForm()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
ComboItems = new ArrayList();
PrepareSourceCombo();
//edStatus.BackColor=Color.FromArgb(255,200,200,200);
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose(disposing);
}
#endregion
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.label1 = new System.Windows.Forms.Label();
this.cbSource = new System.Windows.Forms.ComboBox();
this.edStatus = new System.Windows.Forms.TextBox();
this.btnStopSearch = new System.Windows.Forms.Button();
this.btnImport = new System.Windows.Forms.Button();
this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
this.pBar = new System.Windows.Forms.ProgressBar();
this.lblCurrentOp = new System.Windows.Forms.Label();
this.SuspendLayout();
//
// label1
//
this.label1.Location = new System.Drawing.Point(0, 0);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(100, 16);
this.label1.TabIndex = 1;
this.label1.Text = "Import from:";
//
// cbSource
//
this.cbSource.Dock = System.Windows.Forms.DockStyle.Top;
this.cbSource.Location = new System.Drawing.Point(0, 0);
this.cbSource.Name = "cbSource";
this.cbSource.Size = new System.Drawing.Size(637, 21);
this.cbSource.TabIndex = 2;
this.cbSource.SelectedIndexChanged += new System.EventHandler(this.cbSource_SelectedIndexChanged);
//
// edStatus
//
this.edStatus.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.edStatus.BackColor = System.Drawing.SystemColors.Window;
this.edStatus.ForeColor = System.Drawing.SystemColors.WindowText;
this.edStatus.Location = new System.Drawing.Point(8, 86);
this.edStatus.Multiline = true;
this.edStatus.Name = "edStatus";
this.edStatus.ScrollBars = System.Windows.Forms.ScrollBars.Vertical;
this.edStatus.Size = new System.Drawing.Size(613, 383);
this.edStatus.TabIndex = 3;
this.edStatus.Visible = false;
//
// btnStopSearch
//
this.btnStopSearch.Location = new System.Drawing.Point(206, 19);
this.btnStopSearch.Name = "btnStopSearch";
this.btnStopSearch.Size = new System.Drawing.Size(200, 23);
this.btnStopSearch.TabIndex = 4;
this.btnStopSearch.Text = "Stop searching";
this.btnStopSearch.Visible = false;
this.btnStopSearch.Click += new System.EventHandler(this.btnStopSearch_Click);
//
// btnImport
//
this.btnImport.Enabled = false;
this.btnImport.Location = new System.Drawing.Point(0, 19);
this.btnImport.Name = "btnImport";
this.btnImport.Size = new System.Drawing.Size(200, 23);
this.btnImport.TabIndex = 5;
this.btnImport.Text = "Import";
this.btnImport.Click += new System.EventHandler(this.btnImport_Click);
//
// openFileDialog1
//
this.openFileDialog1.DefaultExt = "sc";
this.openFileDialog1.Filter = "AyaNova files|*.sc|All files|*.*";
this.openFileDialog1.Title = "Open AyaNova database";
//
// pBar
//
this.pBar.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.pBar.Location = new System.Drawing.Point(8, 64);
this.pBar.Name = "pBar";
this.pBar.Size = new System.Drawing.Size(613, 16);
this.pBar.TabIndex = 7;
this.pBar.Visible = false;
//
// lblCurrentOp
//
this.lblCurrentOp.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.lblCurrentOp.Location = new System.Drawing.Point(5, 45);
this.lblCurrentOp.Name = "lblCurrentOp";
this.lblCurrentOp.Size = new System.Drawing.Size(568, 16);
this.lblCurrentOp.TabIndex = 8;
//
// ImportForm
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(637, 482);
this.Controls.Add(this.lblCurrentOp);
this.Controls.Add(this.pBar);
this.Controls.Add(this.btnImport);
this.Controls.Add(this.btnStopSearch);
this.Controls.Add(this.edStatus);
this.Controls.Add(this.cbSource);
this.Controls.Add(this.label1);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;
this.MinimumSize = new System.Drawing.Size(640, 480);
this.Name = "ImportForm";
this.SizeGripStyle = System.Windows.Forms.SizeGripStyle.Show;
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
//Case 433
this.Text = "CEImport version " + AyaBizUtils.DisplayVersion(System.Reflection.Assembly.GetExecutingAssembly().GetName().Version);
this.Load += new System.EventHandler(this.ImportForm_Load);
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private void ImportForm_Load(object sender, System.EventArgs e)
{
#if(!DEBUG)
DialogResult result = MessageBox.Show(
"WARNING: Before you import, be aware that it is your responsibility to do four things:\r\n\r\n" +
"1) Carefully read the documentation regarding importing data from AyaNova CE, \r\n" +
"contact AyaNova technical support if you do not understand any part of the process.\r\n\r\n" +
"Due to numerous changes in the new version of AyaNova some items will not import or \r\n" +
"will import in ways that will be unexpected if you do not read the documentation first.\r\n\r\n" +
"2) Keep a permanent copy of your AyaNova CE database (scdata.sc) in case\r\n" +
"you discover at a later date that something has not imported according to documentation.\r\n\r\n" +
"3) After import, carefully inspect the data that was imported, check that all data you \r\n" +
"expect to see in accordance with the import documentation is present. Do not ignore this step!\r\n\r\n" +
"4) Contact technical support immediately if you discover anything that is not working\r\n" +
"as described in the import utility documentation. ** Do not ** start using the new AyaNova\r\n" +
"program until the problem is resolved with the AyaNova technical support department.\r\n\r\n" +
"Do you understand and accept these responsibilities?", "Warning", MessageBoxButtons.YesNo,
MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
if (result != DialogResult.Yes)
{
this.Close();
return;
}
#endif
GZTW.AyaNova.BLL.AyaBizUtils.Initialize();
if (AyaBizUtils.Trial)
{
MessageBox.Show(
"This utility can only import from a licensed AyaNova CE database\r\n" +
"to a licensed AyaNova database.",
"Incompatible target database", MessageBoxButtons.OK, MessageBoxIcon.Stop);
this.Close();
}
//Case 671
AyaBizUtils.AllowAutomaticMRUOnUpdate = false;
//Case 6 and case 597
AyaBizUtils.AllowAutomaticClosedWorkorderStatus = false;
}
#region app entry point
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new ImportForm());
}
#endregion
#region SourceCombo related
/// <summary>
/// Add default items to combo box
/// </summary>
private void PrepareSourceCombo()
{
bFilling = true;
ComboItems.Clear();
ComboItems.Add(new AyaNovaDBItem("Choose database..."));
ComboItems.Add(new AyaNovaDBItem("<Browse manually for source database>"));
ComboItems.Add(new AyaNovaDBItem("<Auto search for source databases>"));
//Bind the combo box to the array of AyaNovaDBItem objects
//and set which properties of AyaNovaDBItem to use
//as the display and which as the value
cbSource.DataSource = ComboItems;
cbSource.DisplayMember = "Path";
cbSource.ValueMember = "Path";
bFilling = false;
}
private void cbSource_SelectedIndexChanged(object sender, System.EventArgs e)
{
if (bFilling) return;
AyaNovaDBItem ADBSelected = (AyaNovaDBItem)cbSource.SelectedItem;
if (ADBSelected.ControlItem)
{
btnImport.Enabled = false;
if (ADBSelected.Path == "<Browse manually for source database>")
{
BrowseForDB();
}
if (ADBSelected.Path == "<Auto search for source databases>")
{
//SearchForDB();
this.btnStopSearch.Text = "Stop searching";
this.btnStopSearch.Visible = true;
this.edStatus.Visible = true;
lFoundCount = 0;
Thread thrd1;
thrd1 = new Thread(new ThreadStart(SearchForDB));
thrd1.Start();
}
}
else
{
//a db was selected, open it up and display extended info about it
btnImport.Enabled = false;
this.edStatus.Visible = true;
strOldDBPath = ADBSelected.Path;
DisplayDBInfo();
return;
}
}
#endregion
#region Old DB related
/// <summary>
/// Display info about selected database
/// </summary>
private void DisplayDBInfo()
{
int nVersion = 0;
string sRegTo = "";
StringBuilder sb = new StringBuilder();
sb.Append("Information for database:\r\n");
sb.Append(strOldDBPath);
sb.Append("\r\n");
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
// TODO: Modify the connection string and include any
// additional required properties for your database.
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source= " + strOldDBPath;
try
{
//****************************
// code to process data.
conn.Open();
OleDbCommand cmd = new OleDbCommand(
"SELECT defaults.regto, defaults.versioninfo FROM defaults;", conn);
// "SELECT TOP 1 wo.id, wo.created FROM wo ORDER BY wo.id DESC;"
OleDbDataReader rdr;
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
sb.Append("Registered to: ");
sRegTo = rdr.GetString(0);
sb.Append(sRegTo);
sb.Append(" Version:");
sb.Append(rdr.GetString(1));
nVersion = int.Parse(rdr.GetString(1));
sb.Append("\r\n");
}
rdr.Close();
cmd.CommandText = "SELECT TOP 1 wo.id, wo.created FROM wo ORDER BY wo.id DESC;";
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
sb.Append("Last work order ");
sb.Append(rdr.GetInt32(0).ToString());
sb.Append(" created on ");
sb.Append(rdr.GetDateTime(1).ToString());
sb.Append("\r\n");
}
rdr.Close();
if (sRegTo == "SECURITY VIOLATION" || GZHash(sRegTo) == 1575153704/*TEAM ESD*/)
{
sb.Append(
"********************\r\n" +
"This database is Locked due to a security violation and can not be imported!\r\nContact AyaNova tech support to proceed." +
"\r\n********************\r\n");
btnImport.Enabled = false;
}
else
{
#if(DEBUG)
#if(ATTEMPT_UPGRADE_FROM_142)
if (nVersion < 171)
{
//NOTE: this will auto update it the moment you select the source DB and if all goes well then you can import.
UpdateCEDatabaseSchema.go(conn, nVersion);
nVersion = 171;//if this update works then the version is now 171 SET A BREAKPOINT HERE TO SEE IF IT WORKED OR WHATEVER
}
#endif
#endif
if (nVersion > 170)
{
//sb.Append("This database version can be imported\r\n");
btnImport.Enabled = true;
}
else
{
sb.Append("********************\r\nThis database version needs to be upgraded\r\n to 171 or higher before it can be imported\r\n********************\r\n");
btnImport.Enabled = false;
}
}
if (sRegTo.IndexOf("Evaluation") != -1)
{
sb.Append(
"********************************************************\r\n" +
"*** Incompatible source database!\r\n" +
"*** Only a licensed AyaNova CE database can be imported.\r\n" +
"********************************************************\r\n");
btnImport.Enabled = false;
}
//****************************
edStatus.Text = sb.ToString();
}
catch (Exception ex)
{
sb.Append(ex.Message);
MessageBox.Show(ex.Message);
}
finally
{
edStatus.Text = sb.ToString();
conn.Close();
}
}
//Open an openfile dialog and select manually db
//insert into combo box list
//then display info about it
private void BrowseForDB()
{
if (DialogResult.OK == openFileDialog1.ShowDialog())
{
string f = openFileDialog1.FileName;
bFilling = true;
ComboItems.Clear();
cbSource.DataSource = null;
ComboItems.Add(new AyaNovaDBItem("<Browse manually for source database>"));
ComboItems.Add(new AyaNovaDBItem("<Auto search for source databases>"));
//******************************
ComboItems.Add(new AyaNovaDBItem(f, "test", Directory.GetLastWriteTime(f), Directory.GetLastWriteTime(f), 69));
//********************************
cbSource.DataSource = ComboItems;
cbSource.DisplayMember = "Path";
cbSource.ValueMember = "Path";
cbSource.SelectedIndex = 0;
bFilling = false;
cbSource.SelectedIndex = 2;
}
}
//Search all paths available for any scdata.sc files
//and get info about each one then add to list
private void SearchForDB()
{
bFilling = true;
ComboItems.Clear();
cbSource.DataSource = null;
ComboItems.Add(new AyaNovaDBItem("<Browse manually for source database>"));
ComboItems.Add(new AyaNovaDBItem("<Auto search for source databases>"));
string[] sDrives = Directory.GetLogicalDrives();
foreach (string d in sDrives)
{
if (!bContinue)
break;
try
{
if (d != "A:\\")
DirSearch(d, "*.sc");
}
catch (Exception e)
{
string s = e.Message;
}
}
cbSource.DataSource = ComboItems;
cbSource.DisplayMember = "Path";
cbSource.ValueMember = "Path";
bFilling = false;
}
/// <summary>
///
/// </summary>
/// <param name="sDir"></param>
void DirSearch(string sDir, string sPattern)
{
try
{
foreach (string d in Directory.GetDirectories(sDir))
{
foreach (string f in Directory.GetFiles(d, sPattern))
{
ComboItems.Add(new AyaNovaDBItem(f, "test", Directory.GetLastWriteTime(f), Directory.GetLastWriteTime(f), 69));
lFoundCount++;
}
if (bContinue)
{
//scroll up
sOut5 = sOut4;
sOut4 = sOut3;
sOut3 = sOut2;
sOut2 = sOut1;
sOut1 = d;
edStatus.Text = "Found: " + lFoundCount.ToString() + " files.\r\n=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\r\n" + sOut5 + "\r\n" + sOut4 + "\r\n" + sOut3 + "\r\n" + sOut2 + "\r\n" + sOut1;
DirSearch(d, sPattern);
}
else
{
return;
}
}
}
catch (System.Exception excpt)
{
//Ignore any errors'
string nothing = excpt.Message;
//MessageBox.Show(excpt.Message);
}
}
private void btnStopSearch_Click(object sender, System.EventArgs e)
{
_state = OpState.Canceled;
//_calcButton.Enabled = false;
bContinue = false;
this.btnStopSearch.Visible = false;
}
#endregion
#region IMPORT
private void btnImport_Click(object sender, System.EventArgs e)
{
DialogResult result = MessageBox.Show(
"WARNING: If you proceed all existing data in the \r\n" +
"new AyaNova database will be erased prior to importing\r\n" +
"data from the old AyaNova CE database.\r\n\r\n" +
"If you are not certain of the implications of importing,\r\n" +
"please contact support@ayanova.com before proceeding.\r\n\r\n" +
"There is no way to reverse this process once it has started.\r\n\r\n" +
"Are you certain you wish to proceed?", "Warning", MessageBoxButtons.YesNo,
MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
if (result != DialogResult.Yes)
return;
result = MessageBox.Show(
"There is no way to reverse this process once it has started.\r\n\r\n" +
"All existing data in the new AyaNova database *will* be erased.\r\n\r\n" +
"Are you certain you wish to proceed?", "Last chance warning", MessageBoxButtons.YesNo,
MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
if (result != DialogResult.Yes)
return;
if (!DoLogin())
{
MessageBox.Show("You will need to provide the new and old \r\nAyaNova manager account passwords to proceed with import.");
return;
}
//this.btnStopSearch.Text="Stop importing";
//this.btnStopSearch.Visible=true;
this.btnImport.Enabled = false;
this.cbSource.Enabled = false;
this.edStatus.Text = "";
this.edStatus.Visible = true;
pBar.Visible = true;
bContinue = true;
//ShowProgress("STARTING","Starting import...",0, out cancel);
// Asynch delegate method
DoImportDelegate doi = new DoImportDelegate(DoImport);
doi.BeginInvoke(strOldDBPath, null, null);
//ShowProgress("Starting import....");
// Thread ImportThread;
// ImportThread = new Thread(new ThreadStart(DoImport));
// ImportThread.IsBackground=true;
// ImportThread.Start();
}
delegate void DoImportDelegate(string strOldDB);
private void DoImport(string strOldDB)
{
//Used for diagnosis, is always set to the current id number
//of whatever is being imported, if an exception is thrown it
//can be used to find out what record was being imported at the time.
string sLastOp = "Start";
bool cancel = false;
ShowProgress("STARTING", "Starting import...", 0, out cancel);
System.DateTime dtStart = System.DateTime.Now;
//Set to allow importing objects reference users who are inactive temporarily
AyaBizUtils.CE = true;
//No notification processing during import please
AyaBizUtils.GlobalSettings.UseNotification = false;
//Optimization related to avoid needless forced setting of
//db workorder service autonumber field when next wo is one more than
//the last wo imported
int nLastImportedWorkorderNumber = -1;
double lImported = 0;
int lTotalRecords = 0;
//authentication sucessful, begin import now
//scratch string
string sTemp = "";
// //implement some basic caching so parent objects don't need to be continuously reloaded
// //if the parent is the same as last loop
// //int nPreviousWorkorderItemID=0;
// int nPreviousWorkorderID=0;
// int nCurrentID=0;
//Crypto provider
sLastOp = "Instantiating Interop...";
interop.blue.CredClass cryp = new interop.blue.CredClass();
sLastOp = "InteropInstantiated, about to open CE database...";
//OPEN UP DATABASE
ShowProgress("", "Opening old database...", 0, out cancel);
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source= " + strOldDB;
//Second connection Used for workorder items
sLastOp = "Opening snSub...";
System.Data.OleDb.OleDbConnection cnSub = new System.Data.OleDb.OleDbConnection();
cnSub.ConnectionString = conn.ConnectionString;
cnSub.Open();
OleDbCommand cmSub = new OleDbCommand();
cmSub.Connection = cnSub;
cmSub.CommandType = System.Data.CommandType.Text;
OleDbDataReader drSub;
//****************************************
//Third connection (used for workorder item children)
sLastOp = "Opening snSub2...";
System.Data.OleDb.OleDbConnection cnSub2 = new System.Data.OleDb.OleDbConnection();
cnSub2.ConnectionString = conn.ConnectionString;
cnSub2.Open();
OleDbCommand cmSub2 = new OleDbCommand();
cmSub2.Connection = cnSub2;
cmSub2.CommandType = System.Data.CommandType.Text;
OleDbDataReader drSub2;
//****************************************
try
{
sLastOp = "Opening conn...";
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.Text;
OleDbDataReader rdr;
//OleDbDataReader drSub;
//OPEN UP DATABASE
sLastOp = "About to erase target database...";
ShowProgress("", "Erasing new AyaNova database...", 0, out cancel);
// try
// {
DBManager.EraseDatabase("I_UNDERSTAND_ALL_DATA_WILL_BE_ERASED_AND_I_ACCEPT_RESPONSIBILITY");
// }
// catch(Exception ex)
// {
// ShowProgress("!Error erasing new database: " +ex.Message+"\r\n"+ex.InnerException.Message,"",0, out cancel);
// goto BAIL;
// }
sLastOp = "Target erased, starting import...";
dtStart = System.DateTime.Now;
#region Import security groups
/*
*
* Security groups
*
*
* */
ShowProgress("", "Importing Security Groups...", 0, out cancel);
//ShowProgress("","Importing Security Groups...";
cmd.CommandText = "SELECT Count(groups.id) AS TotalRecords FROM groups;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htSecurityGroups = new Hashtable(lTotalRecords);
cmd.CommandText =
"SELECT groups.id, groups.a, groups.b FROM groups;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
sTemp = GetString(rdr, 1);
cryp.Process(ref sTemp, false, 235);//AyaNova keycodes are index 235
SecurityGroup s = SecurityGroup.NewItem();
htSecurityGroups.Add(rdr.GetInt32(0), s.ID);
// if(sTemp=="<< Manager>>")
// sTemp="OLD AyaNova Administrator";
s.Name = sTemp;
sTemp = GetString(rdr, 2);
cryp.Process(ref sTemp, false, 235);//AyaNova keycodes are index 235
sTemp = sTemp.Substring(sTemp.Length - 27);
#region 2023-07-14 migrate to v8 CHANGE
//2023-07-14 migrate to v8 it's v8 now, no one is moving to v7 from ce so I'm skipping all this and just setting them all to full rights
#region original before 2023
////WORKORDERSTAT=0 Main workorder view list screen
////NO EQUIVALENT RIGHT
////WORKORDER=1
////string stest=sTemp.Substring(1,1);
////case 1655
////SetRight(int.Parse(sTemp.Substring(1,1)),"Object.Workorder",s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderItem", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderItemLabor", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderItemMiscExpense", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderItemOutsideService", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderItemPart", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderItemScheduledUser", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderItemTask", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderItemTravel", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderItemLoan", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderService", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderQuote", s);
//SetRight(int.Parse(sTemp.Substring(1, 1)), "Object.WorkorderService.CloseByDate", s);
////SHIPPING=2
////NO EQUIVALENT
////MAIL=3//use=the=mail=system=(no=read=only=here)
//SetRight(int.Parse(sTemp.Substring(3, 1)), "Object.Memo", s);
////DBASEUTILS=4//=use=the=database=utilities
//SetRight(int.Parse(sTemp.Substring(4, 1)), "Object.Global", s);
//SetRight(int.Parse(sTemp.Substring(4, 1)), "Object.Region", s);
//SetRight(int.Parse(sTemp.Substring(4, 1)), "Object.LocalizedText", s);
//SetRight(int.Parse(sTemp.Substring(4, 1)), "Object.TaxCode", s);
////SECURITYGROUPS=5//=rights=to=create=edit=GROUPS
//SetRight(int.Parse(sTemp.Substring(5, 1)), "Object.SecurityGroup", s);
////SEARCH=6//=access=to=the=search=screen
////NO EQUIVALENT
////REPORTS=7//=access=to=the=reporting=screen=not=workorder=printing
//SetRight(int.Parse(sTemp.Substring(7, 1)), "Object.Report", s);
////PRINTING=8//able=to=change=printing=setup
//// NO CURRENT EQUIVALENT
////CLIENTS=9//CLIENTS=SCREEN
//SetRight(int.Parse(sTemp.Substring(9, 1)), "Object.Client", s);
//SetRight(int.Parse(sTemp.Substring(9, 1)), "Object.HeadOffice", s);
//SetRight(int.Parse(sTemp.Substring(9, 1)), "Object.ClientGroup", s);
//SetRight(int.Parse(sTemp.Substring(9, 1)), "Object.Contract", s);
//SetRight(int.Parse(sTemp.Substring(9, 1)), "Object.ContractRate", s);
//SetRight(int.Parse(sTemp.Substring(9, 1)), "Object.ServiceBank", s);
////RATES=10//RATES=SCREEN
//SetRight(int.Parse(sTemp.Substring(10, 1)), "Object.Rate", s);
//SetRight(int.Parse(sTemp.Substring(10, 1)), "Object.RateUnitChargeDescription", s);
////ZONES=11
//SetRight(int.Parse(sTemp.Substring(11, 1)), "Object.DispatchZone", s);
////UNITS=12
//SetRight(int.Parse(sTemp.Substring(12, 1)), "Object.Unit", s);
//SetRight(int.Parse(sTemp.Substring(12, 1)), "Object.UnitMeterReading", s);
////MODELS=13
//SetRight(int.Parse(sTemp.Substring(13, 1)), "Object.UnitModel", s);
////PARTS=14
//SetRight(int.Parse(sTemp.Substring(14, 1)), "Object.Part", s);
//SetRight(2, "Object.Part", s);
//SetRight(2, "Object.PartAssembly", s);
//SetRight(2, "Object.PartCategory", s);
//SetRight(2, "Object.PartByWarehouseInventory", s);
//SetRight(2, "Object.PartWarehouse", s);
//SetRight(2, "Object.PartInventoryAdjustment", s);
//SetRight(2, "Object.PurchaseOrder", s);
//SetRight(2, "Object.UnitOfMeasure", s);
////TASKS=15
//SetRight(int.Parse(sTemp.Substring(15, 1)), "Object.Task", s);
//SetRight(2, "Object.TaskGroup", s);
//SetRight(2, "Object.Task", s);
//SetRight(2, "Object.TaskGroupTask", s);
////PROJECTS=16
//SetRight(int.Parse(sTemp.Substring(16, 1)), "Object.Project", s);
////WOTYPES=17//BLAH
//SetRight(int.Parse(sTemp.Substring(17, 1)), "Object.WorkorderCategory", s);
//SetRight(int.Parse(sTemp.Substring(17, 1)), "Object.UnitServiceType", s);
//SetRight(int.Parse(sTemp.Substring(17, 1)), "Object.WorkorderStatus", s);
//SetRight(int.Parse(sTemp.Substring(17, 1)), "Object.WorkorderItemType", s);
//SetRight(int.Parse(sTemp.Substring(17, 1)), "Object.Priority", s);
////ADDRESSBOOKS=18//MODIFY=ADDRESS=BOOKS (vendors)
//SetRight(int.Parse(sTemp.Substring(18, 1)), "Object.Vendor", s);
////USERS=19//RIGHT=TO=EDIT=USERS=AND=PLACE=IN=GROUPS
//SetRight(int.Parse(sTemp.Substring(19, 1)), "Object.User", s);
//SetRight(int.Parse(sTemp.Substring(19, 1)), "Object.UserCertificationAssigned", s);
//SetRight(int.Parse(sTemp.Substring(19, 1)), "Object.UserSkillAssigned", s);
//SetRight(int.Parse(sTemp.Substring(19, 1)), "Object.UserCertification", s);
//SetRight(int.Parse(sTemp.Substring(19, 1)), "Object.UserSkill", s);
////PM=20//preventative=maintenance=screen
//SetRight(int.Parse(sTemp.Substring(20, 1)), "Object.WorkorderPreventiveMaintenance", s);
////LOANERS=21//loaner=equipment=screen
//SetRight(int.Parse(sTemp.Substring(21, 1)), "Object.LoanItem", s);
////SCHEDULE=22//scheduling=screen
//SetRight(int.Parse(sTemp.Substring(22, 1)), "Object.ScheduleForm", s);
////SCHED_GROUPS=23//schedule=groups=screen
//SetRight(int.Parse(sTemp.Substring(23, 1)), "Object.ScheduleableUserGroup", s);
//SetRight(int.Parse(sTemp.Substring(23, 1)), "Object.ScheduleableUserGroupUser", s);
////SCHED_MARKERS=24//schedule=groups=screen
//SetRight(int.Parse(sTemp.Substring(24, 1)), "Object.ScheduleMarker", s);
////USERPREFS=25//user=preferences
////NO EQUIVALENT
////ARAS_REQUESTS=26//ARAS=service=requests
//SetRight(int.Parse(sTemp.Substring(26, 1)), "Object.ClientServiceRequest", s);
#endregion original
#region 2023 new
//WORKORDERSTAT=0 Main workorder view list screen
//NO EQUIVALENT RIGHT
//WORKORDER=1
//string stest=sTemp.Substring(1,1);
//case 1655
//SetRight(int.Parse(sTemp.Substring(1,1)),"Object.Workorder",s);
SetRight(1, "Object.WorkorderItem", s);
SetRight(1, "Object.WorkorderItemLabor", s);
SetRight(1, "Object.WorkorderItemMiscExpense", s);
SetRight(1, "Object.WorkorderItemOutsideService", s);
SetRight(1, "Object.WorkorderItemPart", s);
SetRight(1, "Object.WorkorderItemScheduledUser", s);
SetRight(1, "Object.WorkorderItemTask", s);
SetRight(1, "Object.WorkorderItemTravel", s);
SetRight(1, "Object.WorkorderItemLoan", s);
SetRight(1, "Object.WorkorderService", s);
SetRight(1, "Object.WorkorderQuote", s);
SetRight(1, "Object.WorkorderService.CloseByDate", s);
//SHIPPING=2
//NO EQUIVALENT
//MAIL=3//use=the=mail=system=(no=read=only=here)
SetRight(1, "Object.Memo", s);
//DBASEUTILS=4//=use=the=database=utilities
SetRight(1, "Object.Global", s);
SetRight(1, "Object.Region", s);
SetRight(1, "Object.LocalizedText", s);
SetRight(1, "Object.TaxCode", s);
//SECURITYGROUPS=5//=rights=to=create=edit=GROUPS
SetRight(1, "Object.SecurityGroup", s);
//SEARCH=6//=access=to=the=search=screen
//NO EQUIVALENT
//REPORTS=7//=access=to=the=reporting=screen=not=workorder=printing
SetRight(1, "Object.Report", s);
//PRINTING=8//able=to=change=printing=setup
// NO CURRENT EQUIVALENT
//CLIENTS=9//CLIENTS=SCREEN
SetRight(1, "Object.Client", s);
SetRight(1, "Object.HeadOffice", s);
SetRight(1, "Object.ClientGroup", s);
SetRight(1, "Object.Contract", s);
SetRight(1, "Object.ContractRate", s);
SetRight(1, "Object.ServiceBank", s);
//RATES=10//RATES=SCREEN
SetRight(1, "Object.Rate", s);
SetRight(1, "Object.RateUnitChargeDescription", s);
//ZONES=11
SetRight(1, "Object.DispatchZone", s);
//UNITS=12
SetRight(1, "Object.Unit", s);
SetRight(1, "Object.UnitMeterReading", s);
//MODELS=13
SetRight(1, "Object.UnitModel", s);
//PARTS=14
SetRight(1, "Object.Part", s);
SetRight(2, "Object.Part", s);
SetRight(2, "Object.PartAssembly", s);
SetRight(2, "Object.PartCategory", s);
SetRight(2, "Object.PartByWarehouseInventory", s);
SetRight(2, "Object.PartWarehouse", s);
SetRight(2, "Object.PartInventoryAdjustment", s);
SetRight(2, "Object.PurchaseOrder", s);
SetRight(2, "Object.UnitOfMeasure", s);
//TASKS=15
SetRight(1, "Object.Task", s);
SetRight(2, "Object.TaskGroup", s);
SetRight(2, "Object.Task", s);
SetRight(2, "Object.TaskGroupTask", s);
//PROJECTS=16
SetRight(1, "Object.Project", s);
//WOTYPES=17//BLAH
SetRight(1, "Object.WorkorderCategory", s);
SetRight(1, "Object.UnitServiceType", s);
SetRight(1, "Object.WorkorderStatus", s);
SetRight(1, "Object.WorkorderItemType", s);
SetRight(1, "Object.Priority", s);
//ADDRESSBOOKS=18//MODIFY=ADDRESS=BOOKS (vendors)
SetRight(1, "Object.Vendor", s);
//USERS=19//RIGHT=TO=EDIT=USERS=AND=PLACE=IN=GROUPS
SetRight(1, "Object.User", s);
SetRight(1, "Object.UserCertificationAssigned", s);
SetRight(1, "Object.UserSkillAssigned", s);
SetRight(1, "Object.UserCertification", s);
SetRight(1, "Object.UserSkill", s);
//PM=20//preventative=maintenance=screen
SetRight(1, "Object.WorkorderPreventiveMaintenance", s);
//LOANERS=21//loaner=equipment=screen
SetRight(1, "Object.LoanItem", s);
//SCHEDULE=22//scheduling=screen
SetRight(1, "Object.ScheduleForm", s);
//SCHED_GROUPS=23//schedule=groups=screen
SetRight(1, "Object.ScheduleableUserGroup", s);
SetRight(1, "Object.ScheduleableUserGroupUser", s);
//SCHED_MARKERS=24//schedule=groups=screen
SetRight(1, "Object.ScheduleMarker", s);
//USERPREFS=25//user=preferences
//NO EQUIVALENT
//ARAS_REQUESTS=26//ARAS=service=requests
SetRight(1, "Object.ClientServiceRequest", s);
#endregion 2023 new
//SECURITY RIGHTS
#endregion 2023-07-14 migrate to v8 changes
s.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Security groups.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END SECURITY GROUPS
//SecurityGroupList sgl = SecurityGroupList.GetList("");
#endregion
#region Import zones
//*********************************************************
// ZONES
ShowProgress("", "Importing zones...", 0, out cancel);
cmd.CommandText = "SELECT Count(zones.id) AS TotalRecords FROM zones;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htZones = new Hashtable(lTotalRecords);
DispatchZones zones = DispatchZones.GetItems(false);
//ShowProgress("\r\nZONES\r\n******************");
cmd.CommandText = "SELECT zones.id, zones.name, zones.zoneinfo FROM zones;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
DispatchZone d = zones.Add();
d.Name = GetString(rdr, 1);
//ShowProgress("Zone: " + d.Name);
d.Description = GetString(rdr, 2);
d.Active = true;
htZones.Add(rdr.GetInt32(0), d.ID);
zones.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END ZONES
ShowProgress("Imported: " + lImported.ToString() + " Dispatch zones.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
zones = null;
//************************************************************************
#endregion
#region Import users
//*********************************************************
// USERS
ShowProgress("", "Importing users...", 0, out cancel);
cmd.CommandText = "SELECT Count(users.id) AS TotalRecords FROM users;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htUsers = new Hashtable(lTotalRecords + 1);
htUsers.Add(1, new Guid("2ECC77FC-69E2-4A7E-B88D-BD0ECAF36AED"));
//ShowProgress("\r\nUSERS\r\n******************");
cmd.CommandText =
"SELECT users.id, users.first, users.last, users.initials, " +
"users.tech, users.active, users.email, users.c, users.defzone " +
"FROM users " +
"WHERE (((users.id)<>1));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
User u = User.NewItem();
u.DefaultLanguage = "English";
htUsers.Add(rdr.GetInt32(0), u.ID);
u.FirstName = GetString(rdr, 1);
u.LastName = GetString(rdr, 2);
u.Initials = GetString(rdr, 3);
//ShowProgress("User: "+u.FirstName + " " + u.LastName);
if (rdr.GetBoolean(4) == true)//is a tech?
u.UserType = UserTypes.Schedulable;
else
u.UserType = UserTypes.NonSchedulable;
//Import all scheduleable users as inactive
//to avoid license issues during import
if (u.UserType == UserTypes.Schedulable)
u.Active = false;
else
u.Active = rdr.GetBoolean(5);
u.EmailAddress = GetString(rdr, 6);
u.RegionID = new Guid("8236E8D1-CAB1-4797-9C34-93861954AE6A");//Default region
if (htZones.Contains(rdr.GetInt32(8)))
u.DispatchZoneID = (Guid)htZones[rdr.GetInt32(8)];
u.MemberOfGroup = (Guid)htSecurityGroups[rdr.GetInt32(7)];// administrators- "FF0DE42A-0EA0-429B-9643-64355703E8D1"
u.Login = u.Initials + System.DateTime.Now.Millisecond.ToString();
u.Password = u.Initials + System.DateTime.Now.Millisecond.ToString();
u.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Users.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END USERS
//************************************************************************
#endregion
#region Import Probstats
//*********************************************************
// PROBSTAT
ShowProgress("", "Importing Workorder Status types...", 0, out cancel);
cmd.CommandText = "SELECT Count(probstat.id) AS TotalRecords FROM probstat;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htWOStatus = new Hashtable(lTotalRecords);
cmd.CommandText =
"SELECT probstat.id, probstat.notes, probstat.red, probstat.green, " +
"probstat.blue FROM probstat ORDER BY probstat.id;";
rdr = cmd.ExecuteReader();
WorkorderStatuses wostatuses = WorkorderStatuses.GetItems();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
WorkorderStatus s = wostatuses.Add();
s.Name = GetString(rdr, 1);
htWOStatus.Add(rdr.GetInt32(0), s.ID);
s.Active = true;
s.ARGB = System.Drawing.Color.FromArgb(rdr.GetInt32(2), rdr.GetInt32(3), rdr.GetInt32(4)).ToArgb();
wostatuses.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Workorder status items.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
wostatuses = null;
//END PROBSTAT
//************************************************************************
#endregion
#region Import wotypes (category)
//*********************************************************
// wotypes
ShowProgress("", "Importing wotypes (categories)...", 0, out cancel);
cmd.CommandText = "SELECT Count(wotypes.id) AS TotalRecords FROM wotypes;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htCategories = new Hashtable(lTotalRecords);
WorkorderCategories wocats = WorkorderCategories.GetItems();
cmd.CommandText =
"SELECT wotypes.id, wotypes.category FROM wotypes;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
WorkorderCategory s = wocats.Add();
s.Name = GetString(rdr, 1);
htCategories.Add(rdr.GetInt32(0), s.ID);
s.Active = true;
wocats.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Workorder Category items.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
wocats = null;
#endregion
#region Import rates
//*********************************************************
// RATES
ShowProgress("", "Importing Rates...", 0, out cancel);
cmd.CommandText = "SELECT Count(rates.id) AS TotalRecords FROM rates;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htRates = new Hashtable(lTotalRecords);
//ShowProgress("\r\nRATES\r\n******************");
cmd.CommandText =
"SELECT rates.id, rates.name, rates.rate, rates.cost, rates.active, " +
"rates.travelrate, rates.partnum FROM rates;";
rdr = cmd.ExecuteReader();
Guid gDefRateUnitChargeDescriptionID = Guid.Empty;
if (lTotalRecords > 0)
{
RateUnitChargeDescriptions rucd = null;
rucd = RateUnitChargeDescriptions.GetItems();
RateUnitChargeDescription rtemp = rucd.Add();
// * COMPROMISE: Created rate unit charge description
// * for imported rates that defaults to "Hour" and "Hours"
// * After import user should modify as required.
rtemp.Name = "Hour";
rtemp.NamePlural = "Hours";
rucd.Save();
gDefRateUnitChargeDescriptionID = rtemp.ID;
}
Rates rates = Rates.GetItems(false);
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Rate r = rates.Add();
htRates.Add(rdr.GetInt32(0), r.ID);
r.Name = GetString(rdr, 1);
//ShowProgress("Rate: " + r.Name);
r.AccountNumber = GetString(rdr, 6);
r.Active = rdr.GetBoolean(4);
r.ClientGroupID = Guid.Empty;
r.Cost = GetDecimal(rdr, 3);
r.Charge = GetDecimal(rdr, 2);
if (rdr.GetBoolean(5) != true)
r.RateType = RateTypes.Service;
else
r.RateType = RateTypes.Travel;
r.RateUnitChargeDescriptionID = gDefRateUnitChargeDescriptionID;
rates.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Rates.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
rates = null;
//END RATES
//************************************************************************
#endregion
#region Import Head offices
//*********************************************************
// HEAD OFFICES
ShowProgress("", "Importing Head offices...", 0, out cancel);
cmd.CommandText = "SELECT Count(clients.id) AS TotalRecords FROM clients WHERE (((clients.isheadoffice)=True));";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htHeadOffices = new Hashtable(lTotalRecords);
cmd.CommandText =
"SELECT clients.id, clients.company, clients.active, " +
"clients.generalnotes, clients.alert, clients.technotes, " +
"clients.acctnumber, clients.streetaddress, " +
"clients.mailaddress, clients.city, clients.stateprov, " +
"clients.postal, clients.country, clients.bizphone, " +
"clients.extension, clients.fax, clients.email, " +
"clients.phone2, clients.phone3, clients.first, clients.last FROM clients WHERE " +
"(((clients.isheadoffice)=True));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
HeadOffice c = HeadOffice.NewItem();
htHeadOffices.Add(rdr.GetInt32(0), c.ID);
c.Name = GetString(rdr, 1);
if (c.Name == "")
c.Name = "_HeadOffice " + rdr.GetInt32(0).ToString() + "(missing name on import)";
c.Active = rdr.GetBoolean(2);
c.Notes = GetString(rdr, 3);
if (GetString(rdr, 4).Length > 0)
c.Notes = c.Notes + "\r\nAyaNova CE Popup notes:\r\n" + GetString(rdr, 4);
if (GetString(rdr, 5).Length > 0)
c.Notes = c.Notes + "\r\nAyaNova CE Tech notes:\r\n" + GetString(rdr, 5);
c.AccountNumber = GetString(rdr, 6);
c.GoToAddress.DeliveryAddress = GetString(rdr, 7);
c.GoToAddress.City = GetString(rdr, 9);
c.GoToAddress.StateProv = GetString(rdr, 10);
c.GoToAddress.Postal = GetString(rdr, 11);
c.GoToAddress.Country = GetString(rdr, 12);
c.MailToAddress.DeliveryAddress = GetString(rdr, 8);
c.MailToAddress.City = GetString(rdr, 9);
c.MailToAddress.StateProv = GetString(rdr, 10);
c.MailToAddress.Postal = GetString(rdr, 11);
c.MailToAddress.Country = GetString(rdr, 12);
c.Email = GetString(rdr, 16);
c.Contact = GetString(rdr, 19) + " " + GetString(rdr, 20);
//bizphone
sTemp = GetString(rdr, 13);
if (sTemp.Length > 0)
{
c.Phone1 = sTemp + AyaBizUtils.SS(" ext. ", GetString(rdr, 14), "");
}
//faxphone
sTemp = GetString(rdr, 15);
if (sTemp.Length > 0)
{
c.Phone2 = sTemp;
}
//biz2
sTemp = GetString(rdr, 17);
if (sTemp.Length > 0)
{
c.Phone3 = sTemp;
}
//biz3
sTemp = GetString(rdr, 18);
if (sTemp.Length > 0)
{
c.Phone4 = sTemp;
}
c.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Head offices
ShowProgress("Imported: " + lImported.ToString() + " HeadOffices.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region Import clients
//*********************************************************
// CLIENTS
ShowProgress("", "Importing Clients...", 0, out cancel);
cmd.CommandText = "SELECT Count(clients.id) AS TotalRecords FROM clients WHERE (((clients.isheadoffice)=False));";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htClients = new Hashtable(lTotalRecords);
cmd.CommandText =
"SELECT clients.id, clients.company, clients.active, " +
"clients.generalnotes, clients.alert, clients.technotes, " +
"clients.acctnumber, clients.streetaddress, " +
"clients.mailaddress, clients.city, clients.stateprov, " +
"clients.postal, clients.country, clients.bizphone, " +
"clients.extension, clients.fax, clients.email, " +
"clients.phone2, clients.phone3, clients.first, " +
"clients.last, clients.headoffice, clients.billheadoffice, " +
"clients.czone FROM clients WHERE " +
"(((clients.isheadoffice)=False));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Client c = Client.NewItem();
htClients.Add(rdr.GetInt32(0), c.ID);
c.Name = GetString(rdr, 1);
if (c.Name == "")
c.Name = "_Client " + rdr.GetInt32(0).ToString() + "(missing name on import)";
c.Active = rdr.GetBoolean(2);
c.Notes = GetString(rdr, 3);
c.PopUpNotes = GetString(rdr, 4);
c.TechNotes = GetString(rdr, 5);
c.AccountNumber = GetString(rdr, 6);
//Head office?
if (htHeadOffices.Contains(SafeGetInt32(rdr, 21)))
{
c.HeadOfficeID = (Guid)htHeadOffices[rdr.GetInt32(21)];
c.BillHeadOffice = rdr.GetBoolean(22);
}
//Dispatch zone
if (htZones.Contains(SafeGetInt32(rdr, 23)))
{
c.DispatchZoneID = (Guid)htZones[rdr.GetInt32(23)];
}
c.MailToAddress.DeliveryAddress = GetString(rdr, 8);
c.MailToAddress.City = GetString(rdr, 9);
c.MailToAddress.StateProv = GetString(rdr, 10);
c.MailToAddress.Postal = GetString(rdr, 11);
c.MailToAddress.Country = GetString(rdr, 12);
c.GoToAddress.DeliveryAddress = GetString(rdr, 7);
if (c.GoToAddress.DeliveryAddress.Length < 2)
c.GoToAddress.DeliveryAddress = c.MailToAddress.DeliveryAddress;
c.GoToAddress.City = GetString(rdr, 9);
c.GoToAddress.StateProv = GetString(rdr, 10);
c.GoToAddress.Postal = GetString(rdr, 11);
c.GoToAddress.Country = GetString(rdr, 12);
c.Email = GetString(rdr, 16);
c.Contact = GetString(rdr, 19) + " " + GetString(rdr, 20);
//bizphone
sTemp = GetString(rdr, 13);
if (sTemp.Length > 0)
{
c.Phone1 = sTemp + AyaBizUtils.SS(" ext. ", GetString(rdr, 14), "");
}
//faxphone
sTemp = GetString(rdr, 15);
if (sTemp.Length > 0)
{
c.Phone2 = sTemp;
}
//biz2
sTemp = GetString(rdr, 17);
if (sTemp.Length > 0)
{
c.Phone3 = sTemp;
}
//biz3
sTemp = GetString(rdr, 18);
if (sTemp.Length > 0)
{
c.Phone4 = sTemp;
}
c.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Clients
ShowProgress("Imported: " + lImported.ToString() + " Clients.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region Import ARAS client logins
//*********************************************************
// ARAS CLIENT ACCOUNTS
ShowProgress("", "Importing ARAS Client Login Accounts...", 0, out cancel);
//
//* COMPROMISE: AyaNova ce's ARAS client accounts have been
//// * superseded by new AyaNova regular user accounts of type "client"
// * All ARAS accounts have been imported as users set as type client
// * with a new security group called "Client login accounts" and defaulted
// * to no rights.
// * After import user will need to adjust rights and set login and passwords.
// *
cmd.CommandText = "SELECT Count(clients_aras.id) AS TotalRecords FROM " +
"clients_aras LEFT JOIN clients ON clients_aras.clientlink " +
"= clients.id WHERE (((clients.id) Is Not Null));";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htARAS = new Hashtable(lTotalRecords);
SecurityGroup sgARAS = null;
//Are there any?
if (lTotalRecords > 0)
{
//then create a new security group for them
sgARAS = SecurityGroup.NewItem();
sgARAS.Name = "Client login accounts";
sgARAS.Save();
}
//ShowProgress("\r\nARAS CLIENT ACCOUNTS\r\n******************");
cmd.CommandText =
"SELECT clients_aras.id, clients_aras.loginid, clients.company " +
"FROM clients_aras LEFT JOIN clients ON clients_aras.clientlink = clients.id WHERE (((clients.company) Is Not Null));";
rdr = cmd.ExecuteReader();
int nclientloginnumber = 0;
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
User u = User.NewItem();
u.DefaultLanguage = "English";
htARAS.Add(rdr.GetInt32(0), u.ID);
//Import old ID value to custom2 field
//so it doesn't conflict with users old id imported to custom 1 field
//u.Custom2=rdr.GetInt32(0).ToString();
u.FirstName = GetString(rdr, 1);
u.LastName = GetString(rdr, 2);
if (u.LastName == "") u.LastName = "Missing company";
u.Initials = "CLNT" + nclientloginnumber.ToString();
nclientloginnumber++;
//ShowProgress("User: "+u.FirstName + " " + u.LastName);
u.UserType = UserTypes.Client;
u.Active = true;
u.RegionID = new Guid("8236E8D1-CAB1-4797-9C34-93861954AE6A");//Default region
u.MemberOfGroup = sgARAS.ID;
u.Login = u.Initials + System.DateTime.Now.Millisecond.ToString();
u.Password = u.Initials + System.DateTime.Now.Millisecond.ToString();
u.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " ARAS client accounts.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END ARAS USERS
//************************************************************************
#endregion
#region Import Client notebook
//*********************************************************
ShowProgress("", "Importing Client Notebook records...", 0, out cancel);
cmd.CommandText = "SELECT Count(contacts.id) AS TotalRecords FROM contacts;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
ClientNoteType cnt = null;
//Are there any?
if (lTotalRecords > 0)
{
ClientNoteTypes cnTemp = ClientNoteTypes.GetItems();
//then create a new note type for them
cnt = cnTemp.Add();
cnt.Name = "AyaNova CE Client notebook import";
cnTemp.Save();
}
//ShowProgress("\r\nCLIENT NOTEBOOK RECORDS\r\n******************");
cmd.CommandText =
"SELECT contacts.id, contacts.date, contacts.staff, " +
"contacts.notes, contacts.clientlink, users.first, " +
"users.last FROM (contacts INNER JOIN users ON contacts.staff " +
"= users.id) INNER JOIN clients ON contacts.clientlink " +
"= clients.id WHERE (((clients.isheadoffice)=False)) " +
"ORDER BY contacts.id;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
ClientNotes cnotes = ClientNotes.GetItems((Guid)htClients[rdr.GetInt32(4)]);
ClientNote cn = cnotes.Add();
//Get new client ID by name
//ClientList is instantiated at end of Import Clients above
cn.ClientID = (Guid)htClients[rdr.GetInt32(4)];
cn.ClientNoteTypeID = cnt.ID;
cn.NoteDate = GetSmartDate(rdr, 1).DBValue;
//Set the note to firstname lastname\r\nNotes
cn.Notes = GetString(rdr, 5) + " " + GetString(rdr, 6) + "\r\n" + GetString(rdr, 3);
//set it's source to be the client record itself
cn.RootObjectID = cn.ClientID;
cn.RootObjectType = RootObjectTypes.Client;
cnotes.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Client notebook records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END
//************************************************************************
#endregion
#region Import Contracts
//*********************************************************
ShowProgress("", "Importing Contracts...", 0, out cancel);
cmd.CommandText = "SELECT Count(contracts.id) AS TotalRecords FROM contracts;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htContracts = new Hashtable(lTotalRecords);
//ShowProgress("\r\nContract RECORDS\r\n******************");
cmd.CommandText =
"SELECT contracts.id, contracts.name, contracts.terms FROM contracts;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Contract c = Contract.NewItem();
htContracts.Add(rdr.GetInt32(0), c.ID);
//contract active follows client active
c.Active = true;
c.ContractRatesOnly = false;
c.DiscountParts = 0m;
c.Name = GetString(rdr, 1);
c.Notes = GetString(rdr, 2);
c.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Contract records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END
//************************************************************************
#endregion
#region Set client contracts
//*********************************************************
ShowProgress("", "Setting client's contracts...", 0, out cancel);
cmd.CommandText = "SELECT Count(clients.id) AS TotalRecords FROM clients WHERE ((Not (clients.contract) Is Null));";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
//ShowProgress("\r\nContract RECORDS\r\n******************");
cmd.CommandText =
"SELECT clients.id, clients.contract, clients.contractexpires " +
"FROM contracts INNER JOIN clients ON contracts.id = clients.contract " +
"WHERE ((Not (clients.contract) Is Null) AND ((clients.isheadoffice)=False));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Client cl = Client.GetItem((Guid)htClients[rdr.GetInt32(0)]);
cl.ContractID = (Guid)htContracts[rdr.GetInt32(1)];
cl.ContractExpires = GetSmartDate(rdr, 2).DBValue;
cl.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Set: " + lImported.ToString() + " client's contract.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END
//************************************************************************
#endregion
#region Import Non-client contacts
//*********************************************************
ShowProgress("", "Importing contacts (Vendors)...", 0, out cancel);
cmd.CommandText = "SELECT Count(nonclients.id) AS TotalRecords FROM nonclients;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htVendors = new Hashtable(lTotalRecords);
Hashtable htWholesalers = new Hashtable();
//ShowProgress("\r\nNon-client (vendor) RECORDS\r\n******************");
cmd.CommandText =
"SELECT nonclients.type, nonclients.company_person, " +
"nonclients.contact, nonclients.address, nonclients.phone, " +
"nonclients.fax, nonclients.email, nonclients.website, " +
"nonclients.notes, nonclients.account, nonclients.id FROM " +
"nonclients ORDER BY nonclients.company_person;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(10).ToString();
Vendor v = Vendor.NewItem();
//----------------
int nVendorType = rdr.GetInt32(0);
switch (nVendorType)
{
case 1://subcontractors
//Deliberately not subcontractor here
//Because in CE it really meant 3rd party repair
//in a new workorder you can select any vendor type but shipping
//for outside service so it's really a moot point anyway
v.VendorType = VendorTypes.ThirdPartyRepair;
htVendors.Add(rdr.GetInt32(10), v.ID);
break;
case 2://Shippers
v.VendorType = VendorTypes.Shipper;
htVendors.Add(rdr.GetInt32(10), v.ID);
break;
case 3://manufacturers/suppliers
//COMPROMISE: AyaNova ce lumped manufacturers and wholesalers together
//They will be imported as both manufacturers and wholesalers (duplicated) in
//separate code below after initial manufacturer import
v.VendorType = VendorTypes.Manufacturer;
htVendors.Add(rdr.GetInt32(10), v.ID);
break;
//no idea where to put this so here it is
//concept is not supported in new version I don't think
case 5://Other resellers (competition)
v.VendorType = VendorTypes.Wholesaler;
htWholesalers.Add(rdr.GetInt32(10), v.ID);
break;
}
v.Name = GetString(rdr, 1);
v.GoToAddress.DeliveryAddress = GetString(rdr, 3, 255);//case 1209
v.MailToAddress.DeliveryAddress = GetString(rdr, 3, 255);//case 1209
v.Notes = GetString(rdr, 8);
v.AccountNumber = GetString(rdr, 9);
v.WebAddress = GetString(rdr, 7);
v.Email = GetString(rdr, 6);
v.Contact = GetString(rdr, 2);
v.Phone1 = GetString(rdr, 4);
v.Phone2 = GetString(rdr, 5);
//---------------
v.Save();
if (v.VendorType == VendorTypes.Manufacturer)
{
v = Vendor.NewItem();
htWholesalers.Add(rdr.GetInt32(10), v.ID);
//Following is same code as above so just
//replace this if changes made, (it was easier this way)
v.Name = GetString(rdr, 1);
v.VendorType = VendorTypes.Wholesaler;
v.GoToAddress.DeliveryAddress = GetString(rdr, 3, 255);//case 1209
v.MailToAddress.DeliveryAddress = GetString(rdr, 3, 255);//case 1209
v.Notes = GetString(rdr, 8);
v.AccountNumber = GetString(rdr, 9);
v.WebAddress = GetString(rdr, 7);
v.Email = GetString(rdr, 6);
v.Contact = GetString(rdr, 2);
v.Phone1 = GetString(rdr, 4);
v.Phone2 = GetString(rdr, 5);
v.Save();
}
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Non-client (vendor) records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END
//************************************************************************
#endregion
#region Import Parts
//*********************************************************
ShowProgress("", "Importing Parts...", 0, out cancel);
cmd.CommandText = "SELECT Count(parts.id) AS TotalRecords FROM parts;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htParts = new Hashtable(lTotalRecords);
Hashtable htPartsRentals = new Hashtable();
//Fetch a list of all unique part id's for parts
//that were rented
Hashtable htTempRentalParts = new Hashtable();
cmd.CommandText =
"SELECT DISTINCT rentals.partlink FROM rentals WHERE (((rentals.partlink)<>0));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
htTempRentalParts.Add(rdr.GetInt32(0), 1);
}
rdr.Close(); if (cancel) goto BAIL;
//ShowProgress("\r\nParts RECORDS\r\n******************");
cmd.CommandText =
"SELECT parts.id, parts.partnumber, parts.description, " +
"parts.supersededby, parts.avgcost, parts.retail, " +
"parts.active, parts.notes, parts.supplier, parts.supplierpart, " +
"parts.upc FROM parts " +
"ORDER BY parts.partnumber;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Part p = Part.NewItem();
//----------------
htParts.Add(rdr.GetInt32(0), p.ID);
p.PartNumber = GetString(rdr, 1);
p.Name = GetString(rdr, 2);
//could be null
p.Alert = SafeGetInt32(rdr, 3).ToString();
p.Cost = GetDecimal(rdr, 4);
p.Retail = GetDecimal(rdr, 5);
p.Active = rdr.GetBoolean(6);
p.Notes = GetString(rdr, 7);
p.UPC = GetString(rdr, 10);
p.WholesalerNumber = GetString(rdr, 9);
//In AyaNova CE there was a single "supplier" field
//since we've imported suppliers into both wholesalers
//and manufacturers separately we need to (if found) insert one of each
//in the appropriate place from the appropriate source
//wholesalers are in htWholesalers
if (htWholesalers.Contains(SafeGetInt32(rdr, 8)))
{
p.WholesalerID = (Guid)htWholesalers[rdr.GetInt32(8)];
}
//Manufacturer - they are in htVendors
//so check for that one
if (htVendors.Contains(SafeGetInt32(rdr, 8)))
{
p.ManufacturerID = (Guid)htVendors[rdr.GetInt32(8)];
}
//---------------
p.Save();
//Add a loan item if this is a rented part
if (htTempRentalParts.Contains(rdr.GetInt32(0)))
{
LoanItem li = LoanItem.NewItem();
htPartsRentals.Add(rdr.GetInt32(0), li.ID);
li.Active = true;
li.Name = PartPickList.GetOnePart(p.ID)[0].DisplayName(AyaBizUtils.GlobalSettings.DefaultPartDisplayFormat);
li.Notes = p.Notes;
li.Save();
}
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Parts records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END
//************************************************************************
#endregion
#region Import Projects
//*********************************************************
ShowProgress("", "Importing Projects...", 0, out cancel);
cmd.CommandText = "SELECT Count(projects.id) AS TotalRecords FROM projects;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htProjects = new Hashtable(lTotalRecords);
//Check for duplicates because projects.name is not indexed unique!@#$%
//if there are, then we are going to append their names with their id value as a string
//to ensure uniqueness, but need to know we did that later on when finding the guid for the
//imported project hence this:
bool bDuplicateProjectNames = false;
if (lTotalRecords > 0)
{
cmd.CommandText = "SELECT First(projects.name) AS [name Field], Count(projects.name) " +
"AS Duplicates FROM projects GROUP BY " +
"projects.name HAVING (((Count(projects.name))>1));";
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
bDuplicateProjectNames = true;
ShowProgress(
"WARNING: duplicate PROJECT names found!\r\n" +
"Importing with unique ID number added to end of name\r\n" +
"to ensure uniqueness. ", "", -1, out cancel);
}
rdr.Close(); if (cancel) goto BAIL;
}
//ShowProgress("\r\nProject RECORDS\r\n******************");
cmd.CommandText =
"SELECT projects.id, projects.name, projects.notes, " +
"projects.active FROM projects ORDER BY projects.name;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Project p = Project.NewItem();
//----------------
htProjects.Add(rdr.GetInt32(0), p.ID);
if (bDuplicateProjectNames == false)
p.Name = GetString(rdr, 1);
else
p.Name = GetString(rdr, 1) + rdr.GetInt32(0).ToString();
p.Active = rdr.GetBoolean(3);
p.Notes = GetString(rdr, 2);
p.ProjectOverseerID = Guid.Empty;
//---------------
p.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Project records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END
//************************************************************************
#endregion
#region Import Schedule groups
//*********************************************************
ShowProgress("", "Importing Schedule groups...", 0, out cancel);
cmd.CommandText = "SELECT Count(schdgrps.id) AS TotalRecords FROM schdgrps;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htScheduleGroups = new Hashtable(lTotalRecords);
//ShowProgress("\r\nSchedule group RECORDS\r\n******************");
cmd.CommandText =
"SELECT schdgrps.id, schdgrps.name FROM schdgrps;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
ScheduleableUserGroup s = ScheduleableUserGroup.NewItem();
//----------------
htScheduleGroups.Add(rdr.GetInt32(0), s.ID);
//save group id number because originally there could be duplicate names
//s.Notes=rdr.GetInt32(0).ToString();
s.Name = GetString(rdr, 1);
//---------------
s.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Schedule group records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END
//************************************************************************
#endregion
#region Import Schedule group users
ShowProgress("", "Importing Schedule group users...", 0, out cancel);
//*********************************************************
cmd.CommandText = "SELECT Count(schdets.grpid) AS TotalRecords FROM (schdgrps " +
"RIGHT JOIN schdets ON schdgrps.id = schdets.grpid) " +
"LEFT JOIN users ON schdets.techid = users.id " +
"WHERE (((users.id) Is Not Null) AND ((schdgrps.id) " +
"Is Not Null));";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
//ShowProgress("\r\nSchedule group user RECORDS\r\n******************");
cmd.CommandText =
"SELECT schdets.grpid, schdets.techid FROM (schdgrps " +
"RIGHT JOIN schdets ON schdgrps.id = schdets.grpid) " +
"LEFT JOIN users ON schdets.techid = users.id " +
"WHERE (((users.id) Is Not Null) AND ((schdgrps.id) " +
"Is Not Null));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
if (htScheduleGroups.Contains(rdr.GetInt32(0)))
{
ScheduleableUserGroup s = ScheduleableUserGroup.GetItem((Guid)htScheduleGroups[rdr.GetInt32(0)]);
ScheduleableUserGroupUser grpuser = s.ScheduleableUsers.Add(s);
grpuser.ScheduleableUserGroupID = s.ID;
grpuser.ScheduleableUserID = (Guid)htUsers[rdr.GetInt32(1)];
//---------------
s.Save();
lImported++;
}
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Schedule group user records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END
//************************************************************************
#endregion
#region Import Schedule markers
ShowProgress("", "Importing Schedule Markers...", 0, out cancel);
//*********************************************************
cmd.CommandText = "SELECT Count(schedmarkers.id) AS TotalRecords FROM schedmarkers;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
cmd.CommandText =
"SELECT schedmarkers.startdate, schedmarkers.enddate, " +
"schedmarkers.notes, schedmarkers.link, schedmarkers.red, " +
"schedmarkers.green, schedmarkers.blue, schedmarkers.id FROM " +
"schedmarkers ORDER BY schedmarkers.startdate;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = (rdr.GetInt32(7)).ToString();
//If it's not a regional marker and the user no longer exists skip it
if (rdr.GetInt32(3) != 0 && !htUsers.Contains(rdr.GetInt32(3)))
continue;
ScheduleMarker m = ScheduleMarker.NewItem();
//----------------
m.ARGB = System.Drawing.Color.FromArgb(rdr.GetInt32(4), rdr.GetInt32(5), rdr.GetInt32(6)).ToArgb();
m.Notes = GetString(rdr, 2);
m.Name = GetString(rdr, 2);
if (rdr.GetInt32(3) == 0)//0=regional
{
m.ScheduleMarkerSourceType = ScheduleMarkerSourceTypes.Regional;
m.SourceID = new Guid("8236E8D1-CAB1-4797-9C34-93861954AE6A");//Default region
}
else
{
m.ScheduleMarkerSourceType = ScheduleMarkerSourceTypes.User;
m.SourceID = (Guid)htUsers[rdr.GetInt32(3)];
}
//---------- DATES ---------------------------
//Added 3.0.2
//Fixup start and stop times so in correct order
//old AyaNova allowed start to be after stop etc
SmartDate sdstart = GetSmartDate(rdr, 0);
SmartDate sdstop = GetSmartDate(rdr, 1);
if (sdstart.Date > sdstop.Date)
{
//then flip them and retrieve stop in start position
//and vice versa
sdstart = GetSmartDate(rdr, 1);
sdstop = GetSmartDate(rdr, 0);
}
//Add a minute to the stop time if they are equal
//because the Businessrule for this doesn't allow
//equal start and stop times
if (DBUtil.DatesAreEqualish(sdstart.Date, sdstop.Date))
sdstop.Date = sdstop.Date.AddMinutes(1);
m.StartDate = sdstart.DBValue;
m.StopDate = sdstop.DBValue;
//case 1968
if (sdstop.Date < dtStart)
m.Completed = true;
//---------------
m.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Schedule marker records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END
//************************************************************************
#endregion
#region Import Tasks
//*********************************************************
// Tasks
ShowProgress("", "Importing Tasks...", 0, out cancel);
cmd.CommandText = "SELECT Count(tasks.id) AS TotalRecords FROM tasks;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
cmd.CommandText = "SELECT tasks.id, tasks.description, tasks.details, " +
"tasks.minutes, tasks.active FROM tasks;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Tasks ts = Tasks.GetItems();
Task t = ts.Add();
//------------------------
t.Active = rdr.GetBoolean(4);
string s = GetString(rdr, 1) + " " + GetString(rdr, 2);
if (s.Length > 255)
t.Name = s.Substring(0, 255);
else
t.Name = s;
//-------------------------
ts.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Tasks
ShowProgress("Imported: " + lImported.ToString() + " Tasks.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region Import Unit model categories
//*********************************************************
// Unit model categories
ShowProgress("", "Importing Unit model categories...", 0, out cancel);
cmd.CommandText = "SELECT Count(unitmodelcats.id) AS TotalRecords FROM unitmodelcats;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htUnitModelCategories = new Hashtable(lTotalRecords);
UnitModelCategories uc = UnitModelCategories.GetItems();
//ShowProgress("\r\nUnit model categories\r\n******************");
cmd.CommandText = "SELECT unitmodelcats.id, unitmodelcats.notes, unitmodelcats.name FROM unitmodelcats;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
UnitModelCategory u = uc.Add();
htUnitModelCategories.Add(rdr.GetInt32(0), u.ID);
//------------------------
u.Active = true;
u.Name = GetString(rdr, 2);
u.Description = GetString(rdr, 1, 255);
//-------------------------
uc.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Unit model categories
ShowProgress("Imported: " + lImported.ToString() + " Unit model categories.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
uc = null;
//************************************************************************
#endregion
#region Import Unit models
//*********************************************************
// Unit model categories
ShowProgress("", "Importing Unit models...", 0, out cancel);
cmd.CommandText = "SELECT Count(unitmodels.id) AS TotalRecords FROM unitmodels;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htUnitModels = new Hashtable(lTotalRecords);
//ShowProgress("\r\nUnit model categories\r\n******************");
cmd.CommandText = "SELECT unitmodels.id, unitmodels.model, unitmodels.description, " +
" unitmodels.lifetimewarranty, unitmodels.warranty, " +
" unitmodels.warrantyterms, unitmodels.discontinued, " +
" unitmodels.discodate, unitmodels.introduced, " +
" unitmodels.active, unitmodels.notes, unitmodels.category, unitmodels.manufacturer " +
"FROM unitmodels;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
UnitModel u = UnitModel.NewItem();
htUnitModels.Add(rdr.GetInt32(0), u.ID);
//------------------------
u.Active = rdr.GetBoolean(9);
u.Discontinued = rdr.GetBoolean(6);
u.DiscontinuedDate = GetSmartDate(rdr, 7).DBValue;
u.IntroducedDate = GetSmartDate(rdr, 8).DBValue;
u.LifeTimeWarranty = rdr.GetBoolean(3);
u.ModelNumber = GetString(rdr, 1);
u.Name = GetString(rdr, 2);
if (u.ModelNumber == "")
{
if (u.ModelNumber == "" && u.Name != "")
u.ModelNumber = u.Name;
else
u.ModelNumber = "IMPORTMISSING CE_DB_ID:" + rdr.GetInt32(0).ToString();
}
u.Notes = GetString(rdr, 10);
if (htUnitModelCategories.Contains(rdr.GetInt32(11)))
u.UnitModelCategoryID = (Guid)htUnitModelCategories[rdr.GetInt32(11)];
if (htVendors.Contains(rdr.GetInt32(12)))
u.VendorID = (Guid)htVendors[rdr.GetInt32(12)];
u.WarrantyLength = rdr.GetInt32(4);
u.WarrantyTerms = GetString(rdr, 5);
//-------------------------
u.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Unit model categories
ShowProgress("Imported: " + lImported.ToString() + " Unit model.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region Import Units
//*********************************************************
// Units
ShowProgress("", "Importing units...", 0, out cancel);
cmd.CommandText = "SELECT Count(units.id) AS TotalRecords FROM units;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htUnits = new Hashtable(lTotalRecords);
Hashtable htLoanItemsUnits = new Hashtable();
//See if there are any units marked as loaner, if so then
//we need to create a client record to import them under
//because the concept of loaner in CE included self owned units
//where in Bamboo it's can be either loaner items or self owned units under
//a self client record so to be safe we'll import them as both and the
//user can delete them afterwards if they are not required
cmd.CommandText = "SELECT Count(units.id) AS LOANERCOUNT FROM units WHERE (((units.loaner)=True));";
rdr = cmd.ExecuteReader();
rdr.Read();
Client cCompany = null;
if (rdr.GetInt32(0) > 0)
{
cCompany = Client.NewItem();
cCompany.Name = "AyaNova CE Loaner unit owner";
cCompany.Notes =
"This client was created to be the owner for imported loaner units\r\n" +
"from AyaNova CE. The CE loaner units were also imported as Loan items\r\n" +
"If you do not want to track service for your own units then you can delete the " +
"units that were imported and then delete this client record.\r\n\r\n" +
"If you do want to track service on your own units you can simply rename this client\r\n" +
"to a more meaningful name like your own company name.";
cCompany.Active = true;
cCompany.Save();
//Keep track of the rental unit company client
//so it can be re-used in PM for units later
htClients.Add(0, cCompany.ID);
}
rdr.Close(); if (cancel) goto BAIL;
//ShowProgress("\r\nUnits\r\n******************");
cmd.CommandText = "SELECT " +
"units.id, units.model, units.sn, units.id1, " +
"units.id2, units.id3, units.boughthere, units.purchasedfrom, " +
"units.receipt, units.purchasedate, units.client, " +
"units.specialwarranty, units.notes, units.pmreport, " +
"units.description, units.loaner, units.lastmeter " +
"FROM units;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Unit u = Unit.NewItem();
htUnits.Add(rdr.GetInt32(0), u.ID);
//------------------------
u.Active = true;
u.BoughtHere = rdr.GetBoolean(6);
//loaner?
if (rdr.GetBoolean(15) == true)
u.ClientID = cCompany.ID;
else
{
if (htClients.Contains(rdr.GetInt32(10)))
u.ClientID = (Guid)htClients[rdr.GetInt32(10)];
}
u.Custom0 = GetString(rdr, 3);
u.Custom1 = GetString(rdr, 4);
u.Custom2 = GetString(rdr, 5);
u.Description = GetString(rdr, 14);
u.Notes = GetString(rdr, 12);
u.PurchasedDate = GetSmartDate(rdr, 9).DBValue;
u.WarrantyTerms = GetString(rdr, 11);
//default is false
u.OverrideModelWarranty = false;
if (u.WarrantyTerms.Length > 0)
{
u.OverrideModelWarranty = true;
//Don't know what to do with this
//u.WarrantyLength
}
//purchased from?
//Give precedence to wholesaler record if present
//else check vendors
if (htWholesalers.Contains(rdr.GetInt32(7)))
u.PurchasedFromID = (Guid)htWholesalers[rdr.GetInt32(7)];
else if (htVendors.Contains(rdr.GetInt32(7)))
u.PurchasedFromID = (Guid)htVendors[rdr.GetInt32(7)];
else
u.PurchasedFromID = Guid.Empty;
u.Receipt = GetString(rdr, 8);
u.ReplacedByUnitID = Guid.Empty;
u.Serial = GetString(rdr, 2);
u.UnitHasOwnAddress = false;
//Model number?
if (htUnitModels.Contains(rdr.GetInt32(1)))
{
u.UnitModelID = (Guid)htUnitModels[rdr.GetInt32(1)];
}
else
u.UnitModelID = Guid.Empty;
//-------------------------
#if(DEBUG)
//if (sLastOp == "164")
// System.Diagnostics.Debugger.Break();
#endif
u.Save();
//If it was a loaner then
//create a loan item record as well
if (cCompany != null && u.ClientID == cCompany.ID)
{
LoanItem li = LoanItem.NewItem();
htLoanItemsUnits.Add(rdr.GetInt32(0), li.ID);
li.Active = true;
li.Serial = u.Serial;
li.Name = UnitNameFetcher.GetUnitNameFromUnitID(u.ID) + " " + u.Description;
li.Notes = u.Notes;
li.Custom0 = u.Description;
li.Save();
}
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Units
ShowProgress("Imported: " + lImported.ToString() + " Units.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region SubUnits
//*********************************************************
// MasterUnits
ShowProgress("", "Importing subunits...", 0, out cancel);
cmd.CommandText = "SELECT Count(subunits.unitid) AS CountOfunitid FROM " +
"subunits WHERE (((subunits.masterunitid)<>0)) HAVING " +
"(((Count(subunits.unitid))<>0));";
rdr = cmd.ExecuteReader();
//todo: echo this for each routine, there
//is no guarantee that rdr.Read will be true
//and the subsequent get will fail if there is no data and
//throw and exeception that will stop the whole import
if (rdr.Read())
lTotalRecords = rdr.GetInt32(0);
else
lTotalRecords = 0;
rdr.Close(); if (cancel) goto BAIL;
cmd.CommandText = "SELECT subunits.unitid, subunits.masterunitid FROM " +
"subunits WHERE (((subunits.unitid)<>0) AND ((subunits.masterunitid)<>0)) " +
"ORDER BY subunits.unitid;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
//Make sure the unit and parent unit both exist...
if (!htUnits.Contains(rdr.GetInt32(0)) || !htUnits.Contains(rdr.GetInt32(1))) continue;
Unit u = Unit.GetItem((Guid)htUnits[rdr.GetInt32(0)]);
u.ParentID = (Guid)htUnits[rdr.GetInt32(1)];
//-------------------------
u.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END
ShowProgress("Imported: " + lImported.ToString() + " SubUnits.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region Workorders
//*********************************************************
// workorders
ShowProgress("", "Importing Work orders...", 0, out cancel);
cmd.CommandText = "SELECT Count(wo.id) AS TotalRecords FROM wo " +
"LEFT JOIN clients ON wo.client = clients.id WHERE (((clients.isheadoffice)=False));";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
Hashtable htWorkorders = new Hashtable(lTotalRecords);
cmd.CommandText = "SELECT " +
"wo.id, wo.project, wo.client, wo.type, wo.closed, " +
"wo.notes, wo.onsite, wo.invoice, wo.created," +
"wo.clientrefnum, wo.clientcontact, wo.status, wo.ourref, " +
"wo.prob_reported, wo.prob_found, wo.action_taken FROM wo " +
"LEFT JOIN clients ON wo.client = clients.id " +
//"WHERE (((clients.isheadoffice)=False)) ORDER BY wo.id;";
"WHERE ( ((clients.isheadoffice)=False)) ORDER BY wo.id;";
//"wo.id, wo.project, wo.client, wo.type, wo.closed, " +
//"wo.notes, wo.onsite, wo.anytime, wo.starttime, " +
//"wo.stoptime, wo.assigntech, wo.quick, wo.invoice, " +
//"wo.creator, wo.modifier, wo.created, wo.modified, " +
//"wo.clientrefnum, wo.clientcontact, wo.status, wo.ourref " +
//"wo.esthours, wo.estrate, wo.indexed, wo.prob_reported, " +
//"wo.prob_found, wo.action_taken, wo.starttime2, " +
//"wo.stoptime2, wo.assigntech2, wo.starttime3, " +
//"wo.stoptime3, wo.assigntech3, wo.starttime4, " +
//"wo.stoptime4, wo.assigntech4, wo.pmid FROM wo "+
//"LEFT JOIN clients ON wo.client = clients.id "+
//"WHERE (((clients.isheadoffice)=False));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Workorder w = Workorder.NewItem(WorkorderTypes.Service);
//------------------------------
//Case 1134
w.CustomerContactName = ".";
w.ClientID = (Guid)htClients[rdr.GetInt32(2)];
int CEWONumber = rdr.GetInt32(0);
htWorkorders.Add(CEWONumber, w.ID);
w.CustomerContactName = GetString(rdr, 10);
w.CustomerReferenceNumber = GetString(rdr, 9);
w.InternalReferenceNumber = GetString(rdr, 12);
w.Onsite = rdr.GetBoolean(6);
//project (can be empty)
if (htProjects.Contains(rdr.GetInt32(1)))
w.ProjectID = (Guid)htProjects[rdr.GetInt32(1)];
//w.RegionID=new Guid("8236E8D1-CAB1-4797-9C34-93861954AE6A");//Default region
w.Summary = GetString(rdr, 5);
//Workorder Category (can be empty)
if (htCategories.Contains(rdr.GetInt32(3)))
w.WorkorderCategoryID = (Guid)htCategories[rdr.GetInt32(3)];
//Children
w.WorkorderService.InvoiceNumber = GetString(rdr, 7);
w.WorkorderService.WorkorderPreventiveMaintenanceWorkorderID = Guid.Empty;
w.WorkorderService.ServiceDate = GetSmartDate(rdr, 8).DBValue;
//w.WorkorderService.ServiceNumber=rdr.GetInt32(0);
//Status (can be empty)
if (htWOStatus.Contains(rdr.GetInt32(11)))
{
w.WorkorderService.WorkorderStatusID = (Guid)htWOStatus[rdr.GetInt32(11)];
}
#region WorkorderItems
//***************************************
// //Open a new reader from a new connection
// System.Data.OleDb.OleDbConnection cnSub = new System.Data.OleDb.OleDbConnection();
// cnSub.ConnectionString = conn.ConnectionString;
// cnSub.Open();
// OleDbCommand cmSub = new OleDbCommand();
// cmSub.Connection=cnSub;
// cmSub.CommandType=System.Data.CommandType.Text;
// OleDbDataReader drSub;
// //****************************************
cmSub.CommandText = "SELECT " +
"probs.wolink, probs.id, probs.brief, probs.notes, " +
"probs.unit, probs.meter, probs.status, probs.pmscheduleid, " +
"probs.taskid, probs.creator, probs.modifier, " +
"probs.created, probs.modified FROM (probs LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients ON wo.client = clients.id " +
"WHERE (((probs.wolink)=" + CEWONumber.ToString() + ") AND ((clients.isheadoffice)=False))" +
"ORDER BY probs.wolink, probs.id;";
drSub = cmSub.ExecuteReader();
//Keep track if first run through loop so we know whether
//to add a woitem or use the initial first one or not
bool bItemsAdded = false;
WorkorderItem wi = null;
while (drSub.Read() && !cancel)
{
if (!bItemsAdded)
{
//It's a workorder that has not yet had any
//workorderitems imported, so it has a default
//blank workorder item already that we will work with
wi = w.WorkorderItems[0];
bItemsAdded = true;
}
else
{
//It's a workorder that has already had a workorder item imported
//so we will need to add a new one
wi = w.WorkorderItems.Add(w);
}
sLastOp = "Prob:" + drSub.GetInt32(1).ToString();
int CEProbNumber = drSub.GetInt32(1);
//------------------------
//preserve old ID value
//htWorkorderItems.Add(drSub.GetInt32(1),wi.ID);
wi.Summary = GetString(drSub, 2);
wi.TechNotes = GetString(drSub, 3);
//New object, just set to unselected
wi.TypeID = Guid.Empty;
//Unit (can be empty)
if (htUnits.Contains(drSub.GetInt32(4)))
wi.UnitID = (Guid)htUnits[drSub.GetInt32(4)];
wi.WarrantyService = false;
//wi.WorkorderItemUnitServiceTypeID=DefaultUnitServiceType.ID;
//Status (can be empty)
if (htWOStatus.Contains(drSub.GetInt32(6)))
wi.WorkorderStatusID = (Guid)htWOStatus[drSub.GetInt32(6)];
sLastOp = "Starting Workorder item child items...";
#region Workorder item children
//***************************************
// //Open a new reader from a new connection
// System.Data.OleDb.OleDbConnection cnSub2 = new System.Data.OleDb.OleDbConnection();
// cnSub2.ConnectionString = conn.ConnectionString;
// cnSub2.Open();
// OleDbCommand cmSub2 = new OleDbCommand();
// cmSub2.Connection=cnSub2;
// cmSub2.CommandType=System.Data.CommandType.Text;
// OleDbDataReader drSub2;
// //****************************************
#region Import Scheduled techs
//*********************************************************
sLastOp = "Starting scheduled techs...";
cmSub2.CommandText = "SELECT wo.id, " +
"wo.starttime, wo.stoptime, wo.assigntech, " +
"wo.starttime2, wo.stoptime2, wo.assigntech2, " +
"wo.starttime3, wo.stoptime3, wo.assigntech3, " +
"wo.starttime4, wo.stoptime4, wo.assigntech4 " +
"FROM wo LEFT JOIN " +
"clients ON wo.client = clients.id WHERE (((wo.id)=" + CEWONumber.ToString() + ") AND ((wo.quick)=False) " +
"AND ((clients.isheadoffice)=False)) " +
"ORDER BY wo.id;";
drSub2 = cmSub2.ExecuteReader();
while (drSub2.Read() && !cancel)
{
sLastOp = "WOSchedTech Wo#:" + drSub2.GetInt32(0).ToString();
int nTechID = 0;
//------------------------
//techs 1 thru 4
for (int x = 1; x < 5; x++)
{
nTechID = drSub2.GetInt32(x * 3);
//change here for v3.0.2
//not allowing import of old sched techs where the techid is selected
//but either date is null
if (nTechID != -1 &&
!drSub2.IsDBNull((x * 3) - 2) &&//stop date
!drSub2.IsDBNull((x * 3) - 1))//start date
{
WorkorderItemScheduledUser s = wi.ScheduledUsers.Add(wi);
if (nTechID == 0 || !htUsers.Contains(nTechID))//Scheduled but no selected user
s.UserID = Guid.Empty;
else
{
s.UserID = (Guid)htUsers[nTechID];
}
//---------- DATES ---------------------------
//Fixup start and stop times so in correct order
//old AyaNova allowed start to be after stop etc
SmartDate sdstart = GetSmartDate(drSub2, (x * 3) - 2);
SmartDate sdstop = GetSmartDate(drSub2, (x * 3) - 1);
if (sdstart.Date > sdstop.Date)
{
//then flip them and retrieve stop in start position
//and vice versa
sdstart = GetSmartDate(drSub2, (x * 3) - 1);
sdstop = GetSmartDate(drSub2, (x * 3) - 2);
}
//Add a minute to the stop time if they are equal
//because the Businessrule for this doesn't allow
//equal start and stop times
if (DBUtil.DatesAreEqualish(sdstart.Date, sdstop.Date))
sdstop.Date = sdstop.Date.AddMinutes(1);
s.StartDate = sdstart.DBValue;
s.StopDate = sdstop.DBValue;
//-----------------------------------------------
}
}
//-------------------------
}//end of sched techs loop
drSub2.Close(); if (cancel) goto BAIL;
//END Scheduled techs
sLastOp = "Done scheduled techs, starting labor";
//************************************************************************
#endregion
#region Import Labor
//*********************************************************
// labor
cmSub2.CommandText =
"SELECT probs.wolink AS WOID, probs.id AS WOITEMID, " +
"labor.tech, labor.hours, labor.nchours, labor.travhours, " +
"labor.rate, labor.travrate, labor.start, labor.stop, " +
"labor.details, labor.distance, labor.id FROM ((labor " +
"LEFT JOIN probs ON labor.link = probs.id) LEFT JOIN " +
"wo ON probs.wolink = wo.id) LEFT JOIN clients ON " +
"wo.client = clients.id WHERE ( " +
"((probs.id)=" + CEProbNumber.ToString() + ") AND " +
"((labor.tech) Is Not Null) AND " +
"((labor.rate) Is Not Null) AND " +
"((clients.isheadoffice)=False)) " +
"ORDER BY probs.wolink;";
drSub2 = cmSub2.ExecuteReader();
while (drSub2.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = "Labor:" + drSub2.GetInt32(12).ToString();
//only import if there are actual dates
//and not null values
if (!drSub2.IsDBNull(9) &&//stop date
!drSub2.IsDBNull(8))//start date)
{
//IMPORT LABOR (SERVICE) HOURS AND DETAILS
WorkorderItemLabor wil = wi.Labors.Add(wi);
wil.NoChargeQuantity = (decimal)drSub2.GetFloat(4);
wil.ServiceDetails = GetString(drSub2, 10);
//regular rate
if (htRates.Contains(drSub2.GetInt32(6)))
wil.ServiceRateID = (Guid)htRates[drSub2.GetInt32(6)];
wil.ServiceRateQuantity = (decimal)drSub2.GetFloat(3);
//---------- DATES ---------------------------
//Fixup start and stop times so in correct order
//old AyaNova allowed start to be after stop etc
SmartDate sdstart = GetSmartDate(drSub2, 8);
SmartDate sdstop = GetSmartDate(drSub2, 9);
if (sdstart.Date > sdstop.Date)
{
//then flip them and retrieve stop in start position
//and vice versa
sdstart = GetSmartDate(drSub2, 9);
sdstop = GetSmartDate(drSub2, 8);
}
//Add a minute to the stop time if they are equal
//because the Businessrule for this doesn't allow
//equal start and stop times
if (DBUtil.DatesAreEqualish(sdstart.Date, sdstop.Date))
sdstop.Date = sdstop.Date.AddMinutes(1);
wil.ServiceStartDate = sdstart.DBValue;
wil.ServiceStopDate = sdstop.DBValue;
//-----------------------------------------------
if (htUsers.Contains(drSub2.GetInt32(2)))
wil.UserID = (Guid)htUsers[drSub2.GetInt32(2)];
//IMPORT TRAVEL HOURS AND RELATED ETC
//are there any travel hours or distance entered?
if ((decimal)drSub2.GetFloat(5) != 0 || (decimal)drSub2.GetFloat(11) != 0)
{
//yes, more than or less than zero travel hours or there is a distance value entered
WorkorderItemTravel wit = wi.Travels.Add(wi);
wit.Distance = (decimal)drSub2.GetFloat(11);
if (htRates.Contains(drSub2.GetInt32(7)))
wit.TravelRateID = (Guid)htRates[drSub2.GetInt32(7)];
wit.TravelRateQuantity = (decimal)drSub2.GetFloat(5);
//There is no separate travel stop and start in AyaNova CE
//but this will barf if they are not set so defaulting
//to the labor start and stop dates which is inherently wrong
//but "more than adequate" (tm) ;)
wit.TravelStartDate = wil.ServiceStartDate;
wit.TravelStopDate = wil.ServiceStopDate;
wit.UserID = wil.UserID;
}
//-------------------------
}//end of if dates valid
}//End labour loop
drSub2.Close(); if (cancel) goto BAIL;
sLastOp = "Done scheduled labor, starting parts";
//************************************************************************
#endregion
#region WoParts
//*********************************************************
// Part
cmSub2.CommandText =
"SELECT probs.wolink AS WOID, woparts.link AS WOITEMID, " +
"woparts.partnum, woparts.misc, woparts.quantity, " +
"woparts.price, woparts.usedby, woparts.sn, woparts.cost, woparts.id " +
"FROM ((woparts " +
"LEFT JOIN probs ON woparts.link = probs.id) LEFT " +
"JOIN wo ON probs.wolink = wo.id) LEFT JOIN clients " +
"ON wo.client = clients.id WHERE ( " +
"((probs.id)=" + CEProbNumber.ToString() + ") AND " +
"((clients.isheadoffice)=False)) " +
"ORDER BY probs.wolink;";
drSub2 = cmSub2.ExecuteReader();
//IF a part is found to be missing then
//a placeholder one will be created and this
//guid will contain it's id
Guid gMissingPartID = Guid.Empty;
while (drSub2.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = "WOPart:" + drSub2.GetInt32(9).ToString();
//IMPORT Part
//see if it's a misc or a regular part first...
if (drSub2.GetInt32(2) == 0)//partnum field (which is actually the parts.id field link) is zero
{
//it's misc part...
WorkorderItemMiscExpense me = wi.Expenses.Add(wi);
me.ChargeAmount = GetDecimal(drSub2, 5) * (decimal)drSub2.GetFloat(4);//charge * quantity since new ayanova has no quantity for m.e.
me.Description = ((decimal)drSub2.GetFloat(4)).ToString() + " @ " + GetDecimal(drSub2, 5).ToString("C");
me.ChargeToClient = true;
me.Name = GetString(drSub2, 3);
me.ReimburseUser = false;
me.TaxPaid = 0;
me.TotalCost = me.ChargeAmount;
if (htUsers.Contains(drSub2.GetInt32(6)))
me.UserID = (Guid)htUsers[drSub2.GetInt32(6)];
}
else
{
//it's an "inventory" part...
WorkorderItemPart wp = wi.Parts.Add(wi);
wp.Cost = GetDecimal(drSub2, 8);
if (htParts.Contains(drSub2.GetInt32(2)))
wp.PartID = (Guid)htParts[drSub2.GetInt32(2)];
else
{
//A part is missing, this should not be happening
//and wouldn't with referential integrity,
//unfortunately... :(
if (gMissingPartID == Guid.Empty)
{
Part p = Part.NewItem();
p.Name = "IMPORT PLACEHOLDER FOR MISSING PART";
p.Notes = "During import a workorder had a part set that doesn't actually exist\r\n" +
"in the parts table. This placehoder was created and used instead\r\n" +
"so that the workorder could be imported.\r\n";
p.PartNumber = "MISSING";
p.Retail = 0m;
p.Save();
gMissingPartID = p.ID;
}
wp.PartID = gMissingPartID;
}
wp.PartWarehouseID = PartWarehouse.DefaultWarehouseID;
wp.Price = GetDecimal(drSub2, 5);
//wp.PurchaseOrderID=Guid.Empty;
wp.Quantity = (decimal)drSub2.GetFloat(4);
wp.Description = GetString(drSub2, 7);
}
}//end part loop
drSub2.Close(); if (cancel) goto BAIL;
sLastOp = "Done parts, starting subrepair...";
//END Part
//************************************************************************
#endregion workorder parts
#region Subrepair
//*********************************************************
// Subrepair
cmSub2.CommandText =
"SELECT probs.wolink, subrepair.where, subrepair.sent, " +
"subrepair.isback, subrepair.eta, subrepair.received, " +
"subrepair.sentvia, subrepair.waybill, subrepair.rma, " +
"subrepair.cost, subrepair.charge, subrepair.notes, subrepair.link, subrepair.id " +
"FROM ((subrepair " +
"LEFT JOIN probs ON subrepair.link = probs.id) " +
"LEFT JOIN wo ON probs.wolink = wo.id) LEFT JOIN " +
"clients ON wo.client = clients.id WHERE ( " +
"((probs.id)=" + CEProbNumber.ToString() + ") AND " +
"((clients.isheadoffice)=False));";
drSub2 = cmSub2.ExecuteReader();
while (drSub2.Read() && !cancel)
{
sLastOp = "Subrepair:" + drSub2.GetInt32(13).ToString();
wi.OutsideService.DateETA = GetSmartDate(drSub2, 4).DBValue;
CSLA.SmartDate sdValue = GetSmartDate(drSub2, 5);//drSub2.GetDateTime(5);
if (sdValue.IsEmpty || sdValue.Date.Year < 1969)
wi.OutsideService.DateReturned = new CSLA.SmartDate().DBValue;
else
wi.OutsideService.DateReturned = sdValue.DBValue;
wi.OutsideService.DateSent = GetSmartDate(drSub2, 2).DBValue;
wi.OutsideService.Notes = GetString(drSub2, 11);
//wi.OutsideService.ReceivedBack=drSub2.GetBoolean(3);
wi.OutsideService.RepairCost = GetDecimal(drSub2, 9);
wi.OutsideService.RepairPrice = GetDecimal(drSub2, 10);
wi.OutsideService.RMANumber = GetString(drSub2, 8);
wi.OutsideService.ShippingCost = 0.0M;
wi.OutsideService.ShippingPrice = 0.0M;
wi.OutsideService.TrackingNumber = GetString(drSub2, 7);
//Vendor sent to...
if (htVendors.Contains(drSub2.GetInt32(1)))
wi.OutsideService.VendorSentToID = (Guid)htVendors[drSub2.GetInt32(1)];
else if (htWholesalers.Contains(drSub2.GetInt32(1)))
wi.OutsideService.VendorSentToID = (Guid)htWholesalers[drSub2.GetInt32(1)];
//Vendor sent via (shipper)...
if (htVendors.Contains(drSub2.GetInt32(6)))
wi.OutsideService.VendorSentViaID = (Guid)htVendors[drSub2.GetInt32(6)];
}//end outside service loop
drSub2.Close(); if (cancel) goto BAIL;
sLastOp = "Done Subrepair";
//END Subrepair
//************************************************************************
#endregion subrepair / outside service
#endregion workorderitem children
}//Bottom of problem items loop
drSub.Close(); if (cancel) goto BAIL;
sLastOp = "Done problem items";
//END Probs
//************************************************************************
#endregion problem items / workorder items
//ONE CLIENT USES THE PROBREPORTED, PROBFOUND, ACTIONTAKEN FIELDS
//THEY WERE TOLD THEY
//WOULD NEVER BE ABLE TO UPGRADE IN FUTURE AND I CAN'T SEE A GOOD PLACE
//TO PUT THAT DATA SO IT'S OUT FOR NOW UNTIL THEY ASK AND WE DETERMINE
//WHERE THEY WANT IT TO GO
//------------------------------
//case 1709
//This can fail here because it has too be valid to set service completed so
//rather than just bomb the whole thing for one bad workorder check first and just log it as unsaveable and move on
if (!w.IsValid)
{
ShowProgress("Source workorder " + CEWONumber.ToString() + " not valid, can't be imported (" + w.BrokenRulesText + ")", "", (int)((lImported / lTotalRecords) * 100), out cancel);
continue;
}
sLastOp = "Saving workorder" + w.WorkorderService.ServiceNumber.ToString();
//Added 3.0.1
w.ServiceCompleted = rdr.GetDateTime(4).Year != 1968;
w.Closed = w.ServiceCompleted;
//-------------
//Optimization related to avoid needless forced setting of
//db workorder service autonumber field when next wo is one more than
//the last wo imported
if ((nLastImportedWorkorderNumber + 1) != CEWONumber)
{
WorkorderService.SetVisibleIDNumber(CEWONumber);
}
w.WorkorderService.ServiceNumber = 0;
w.Save();
nLastImportedWorkorderNumber = CEWONumber;
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
ShowProgress("Imported: " + lImported.ToString() + " Work orders.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//END Workorders
//************************************************************************
#endregion
#region Mail
//*********************************************************
// Mails
ShowProgress("", "Importing mail messages...", 0, out cancel);
//changed v 3.0.2 to only count importable messages
cmd.CommandText = "SELECT Count(mailroute.recipient) AS TotalRecords FROM " +
"users INNER JOIN (users AS users_1 INNER JOIN " +
"(mailroute INNER JOIN mail ON mailroute.maillink = " +
"mail.id) ON users_1.id = mail.from) ON users.id = " +
"mailroute.recipient;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
//changed for v 3.0.2
//mail can exist with no existing recipient or sender
//the query was changed to weed them out
cmd.CommandText =
"SELECT mail.from, mailroute.recipient, mail.date, mailroute.remind, " +
" mail.subject, mail.message, mail.id, " +
" mail.popup FROM users AS users_1 INNER JOIN (users " +
"INNER JOIN (mailroute INNER JOIN mail ON mailroute.maillink " +
"= mail.id) ON users.id = mailroute.recipient) " +
"ON users_1.id = mail.from WHERE (((users.id) " +
"Is Not Null) AND ((users_1.id) Is Not Null));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(6).ToString();
//Guid gFrom;
//Guid gTo;
Memo m = Memo.NewItem();
//Mail from...
if (htUsers.Contains(rdr.GetInt32(0)))
m.FromID = (Guid)htUsers[rdr.GetInt32(0)];
//Mail to...
if (htUsers.Contains(rdr.GetInt32(1)))
m.ToID = (Guid)htUsers[rdr.GetInt32(1)];
sTemp = GetString(rdr, 5);
cryp.Process(ref sTemp, false, 40);//Mail uses it's own offset of 40
m.Message = sTemp;
sTemp = GetString(rdr, 4);
cryp.Process(ref sTemp, false, 40);//Mail uses it's own offset of 40
m.Subject = sTemp;
m.zInternal = GetSmartDate(rdr, 2);
//viewed = NOT Popup (popup=true meant unread in CE)
m.Viewed = !rdr.GetBoolean(7);
//-------------------------
m.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Mails
ShowProgress("Imported: " + lImported.ToString() + " Mail messages.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region Import Rentals 'n loaners
//*********************************************************
// Rentals
ShowProgress("", "Importing Rentals...", 0, out cancel);
cmd.CommandText =
"SELECT Count(rentals.id) AS CountOfid FROM rentals " +
"WHERE ( ((rentals.clientlink) Is Not Null And (rentals.clientlink)<>0) " +
"AND ( ((rentals.partlink) Is " +
"Not Null And (rentals.partlink)<>0) OR ((rentals.unitlink) " +
"Is Not Null And (rentals.unitlink)<>0) ));";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
cmd.CommandText =
"SELECT rentals.clientlink, rentals.id, rentals.partlink, " +
" rentals.unitlink, rentals.dateout, rentals.datedue, " +
" rentals.datereturn, rentals.charges, rentals.notes, " +
" rentals.ref, rentals.returned " +
"FROM rentals WHERE (((rentals.clientlink) " +
"Is Not Null And (rentals.clientlink)<>0) " +
"AND ((rentals.partlink) Is Not Null And (rentals.partlink)<>0)) " +
"OR (((rentals.clientlink) Is Not " +
"Null And (rentals.clientlink)<>0) AND ((rentals.unitlink) " +
"Is Not Null And (rentals.unitlink)<>0)) " +
"ORDER BY rentals.clientlink, rentals.id;";
rdr = cmd.ExecuteReader();
int nCurrentClientID = 0;
int nLastClientID = 0;
Workorder wRental = null;
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(1).ToString();
nCurrentClientID = rdr.GetInt32(0);
bool bReturned = rdr.GetBoolean(10);
//---------- DATES ---------------------------
SmartDate sdout = GetSmartDate(rdr, 4);
SmartDate sddue = GetSmartDate(rdr, 5);
SmartDate sdreturned = GetSmartDate(rdr, 6);
//-----------------------------------------------
if (nCurrentClientID != nLastClientID)
{
nLastClientID = nCurrentClientID;
//See if we can close the last workorder
//if it has no outstanding loaners on it
if (wRental != null)
{
bool bCloseable = true;
foreach (WorkorderItemLoan wil in wRental.WorkorderItems[0].Loans)
{
if (wil.ReturnDate == null || wil.ReturnDate == System.DBNull.Value)
{
bCloseable = false;
break;
}
}
if (bCloseable)
{
wRental.ServiceCompleted = true;
wRental.Closed = true;
wRental.Save();
}
}
//NEW Workorder...
wRental = Workorder.NewItem(WorkorderTypes.Service);
wRental.ClientID = (Guid)htClients[rdr.GetInt32(0)];
wRental.Onsite = false;
wRental.Summary = "AyaNovaCE imported rental items consolidated by client";
wRental.WorkorderItems[0].Summary = "Consolidated rental items imported";
}
//set this each time so at the last it's set to the last most current
//rental settings since the query is sorted by rental ID
wRental.CustomerReferenceNumber = GetString(rdr, 9);
wRental.WorkorderService.ServiceDate = sdout.DBValue;
WorkorderItemLoan l = wRental.WorkorderItems[0].Loans.Add(wRental.WorkorderItems[0]);
l.Charges = rdr.GetDecimal(7);
l.DueDate = sddue.DBValue;
//Is it a unit or a part?
Guid gLoanItemID = Guid.Empty;
if (rdr.GetInt32(3) != 0)//Unit?
{
gLoanItemID = (Guid)htLoanItemsUnits[rdr.GetInt32(3)];
}
else//It's a part, query above guarantees one or the other
{
gLoanItemID = (Guid)htPartsRentals[rdr.GetInt32(2)];
}
l.LoanItemID = gLoanItemID;
l.Notes = GetString(rdr, 9) + "" + GetString(rdr, 8);
l.OutDate = sdout.DBValue;
if (bReturned)
l.ReturnDate = sdreturned.DBValue;
//-------------------------
wRental.Save();
lImported++;
}
//See if we can close the last workorder
//if it has no outstanding loaners on it
//REPEATED HERE because it would not get the last one
//inside the loop above otherwise
if (wRental != null)
{
bool bCloseable = true;
foreach (WorkorderItemLoan wil in wRental.WorkorderItems[0].Loans)
{
if (wil.ReturnDate == null || wil.ReturnDate == System.DBNull.Value)
{
bCloseable = false;
break;
}
}
if (bCloseable)
{
wRental.ServiceCompleted = true;
wRental.Closed = true;
wRental.Save();
}
}
rdr.Close(); if (cancel) goto BAIL;
//END Rentals
ShowProgress("Imported: " + lImported.ToString() + " Rentals/Loaner.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
//keep track of pmHeadID/pmworkorder Guid values
//so parts can be imported into them when all the pm's
//have been imported
Hashtable htPM = new Hashtable();
#region Import Preventive maintenance CLIENT BASED
//*********************************************************
// CLIENT PM
ShowProgress("", "Importing Preventive maintenance CLIENT BASED...", 0, out cancel);
int nNewWorkorderStatus = 0;
cmd.CommandText = "SELECT defaults.wonewstat FROM defaults;";
rdr = cmd.ExecuteReader();
if (rdr.Read())
nNewWorkorderStatus = rdr.GetInt32(0);
rdr.Close();
cmd.CommandText = "SELECT Count(pmhead.id) AS CountOfid FROM pmhead INNER " +
"JOIN clients ON pmhead.link = clients.id;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
cmd.CommandText = "SELECT " +
"pmhead.id, pmhead.active, pmhead.description, " +
"pmhead.notes, pmhead.link, pmhead.woconvert, pmhead.nextsrvdate, " +
"pmhead.repeatevery AS MONTHS, pmhead.rptweeks, " +
"pmhead.rptdays, pmhead.dow, pmhead.tech, " +
"pmhead.wotype, pmhead.woproject, pmhead.woonsite, " +
"pmhead.starttime, pmhead.endtime FROM pmhead " +
"INNER JOIN clients ON pmhead.link = clients.id WHERE " +
"(((pmhead.isclient)=True) AND ((pmhead.autowo)=True));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Workorder w = Workorder.NewItem(WorkorderTypes.PreventiveMaintenance);
//------------------------
w.ClientID = (Guid)htClients[rdr.GetInt32(4)];
htPM.Add(rdr.GetInt32(0), w.ID);
w.Onsite = rdr.GetBoolean(14);
//Project
if (htProjects.Contains(rdr.GetInt32(13)))
w.ProjectID = (Guid)htProjects[rdr.GetInt32(13)];
//w.RegionID=new Guid("8236E8D1-CAB1-4797-9C34-93861954AE6A");//Default region
w.Summary = GetString(rdr, 2);
//Category (type)
if (htCategories.Contains(rdr.GetInt32(12)))
w.WorkorderCategoryID = (Guid)htCategories[rdr.GetInt32(12)];
WorkorderItem wi = w.WorkorderItems[0];
wi.Summary = w.Summary;
wi.TechNotes = GetString(rdr, 3);
wi.TypeID = Guid.Empty;
w.WorkorderPreventiveMaintenance.Active = rdr.GetBoolean(1);
WorkorderItemScheduledUser wisu = wi.ScheduledUsers.Add(wi);
if (htUsers.Contains(rdr.GetInt32(11)))
wisu.UserID = (Guid)htUsers[rdr.GetInt32(11)];
//Get the start date from next service date
//get the start time from startime and end time from endtime
//munge together, add a little salt and wala!
//START
System.DateTime nsd = GetSmartDate(rdr, 6).Date;
System.DateTime nst = GetSmartDate(rdr, 15).Date;
System.DateTime nsdstart = new DateTime(nsd.Year, nsd.Month, nsd.Day, nst.Hour, nst.Minute, 0);
wisu.StartDate = new SmartDate(nsdstart).DBValue;
//STOP
System.DateTime nststop = GetSmartDate(rdr, 16).Date;
System.DateTime nsdstop = new DateTime(nsd.Year, nsd.Month, nsd.Day, nststop.Hour, nststop.Minute + 1, 0);
wisu.StopDate = new SmartDate(nsdstop).DBValue;
int nID = rdr.GetInt32(10);
switch (nID)
{
case 0://ANY DAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.AnyDayOfWeek;
break;
case 1://MONDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Monday;
break;
case 2://TUESDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Tuesday;
break;
case 31://WEDNESDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Wednesday;
break;
case 4://THURSDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Thursday;
break;
case 5://FRIDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Friday;
break;
case 6://SATURDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Saturday;
break;
case 7://SUNDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Sunday;
break;
}
//calculate time span
//if there are any days then it's all in days
//if there are no days but weeks then it's all in days
//if there are no week or days then it's all in months
//In the new AyaNova only hours days months or years are valid timespans
if (rdr.GetInt32(9) != 0)//Days
{
//Set to days plus months *29.5 + weeks * 7 converted to int32
w.WorkorderPreventiveMaintenance.GenerateSpan = System.Convert.ToInt32(rdr.GetInt32(9) + (rdr.GetInt32(7) * 29.5) + (rdr.GetInt32(8) * 7));//whole days only so convert to int
w.WorkorderPreventiveMaintenance.GenerateSpanUnit = AyaUnitsOfTime.Days;
}
else if (rdr.GetInt32(8) != 0)//Weeks
{
//Convert the weeks and months to days
//An average month is 29.5 days long, but we can only handle ints
//so multiply month by 29.5 and convert to int32
w.WorkorderPreventiveMaintenance.GenerateSpan = System.Convert.ToInt32((rdr.GetInt32(7) * 29.5) + (rdr.GetInt32(8) * 7));
w.WorkorderPreventiveMaintenance.GenerateSpanUnit = AyaUnitsOfTime.Days;
}
else if (rdr.GetInt32(7) != 0)//Months
{
if (rdr.GetInt32(7) == 12)
{
w.WorkorderPreventiveMaintenance.GenerateSpanUnit = AyaUnitsOfTime.Years;
w.WorkorderPreventiveMaintenance.GenerateSpan = 1;
}
else
{
w.WorkorderPreventiveMaintenance.GenerateSpanUnit = AyaUnitsOfTime.Months;
w.WorkorderPreventiveMaintenance.GenerateSpan = rdr.GetInt32(7);
}
}
w.WorkorderPreventiveMaintenance.ThresholdSpan = System.Convert.ToInt32(rdr.GetInt32(5));
w.WorkorderPreventiveMaintenance.ThresholdSpanUnit = AyaUnitsOfTime.Days;
if (htWOStatus.Contains(nNewWorkorderStatus))
w.WorkorderPreventiveMaintenance.WorkorderStatusID = (Guid)htWOStatus[nNewWorkorderStatus];
w.WorkorderPreventiveMaintenance.NextServiceDate = GetSmartDate(rdr, 6).DBValue;
//-------------------------
w.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Tasks
ShowProgress("Imported: " + lImported.ToString() + " Preventive Maintenance records for CLIENT (TIME BASED) records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region Import Preventive maintenance UNITS (TIME / AGE BASED)
//*********************************************************
// UNIT TIME/AGE PM'S
//PMHEAD.TYPE<>1
ShowProgress("", "Importing Preventive maintenance UNITS (TIME / AGE BASED)...", 0, out cancel);
cmd.CommandText = "SELECT Count(pmhead.id) AS CountOfid FROM pmhead INNER " +
"JOIN units ON pmhead.link = units.id WHERE (((pmhead.isclient)=False) AND ((pmhead.ismodel)=False) " +
"AND ((pmhead.autowo)=True) " +
"AND ((pmhead.type)<>1));";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
cmd.CommandText = "SELECT " +
"pmhead.id, pmhead.active, pmhead.description, " +
"pmhead.notes, pmhead.link, pmhead.woconvert, pmhead.nextsrvdate, " +
"pmhead.repeatevery AS MONTHS, pmhead.rptweeks, " +
"pmhead.rptdays, pmhead.dow, pmhead.tech, " +
"pmhead.wotype, pmhead.woproject, pmhead.woonsite, " +
"pmhead.starttime, pmhead.endtime, units.client, pmhead.agemonths, pmhead.ageyears FROM pmhead " +
"INNER JOIN units ON pmhead.link = units.id WHERE " +
"(((pmhead.isclient)=False) AND ((pmhead.autowo)=True) AND ((pmhead.type)<>1) AND ((pmhead.ismodel)=False));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Workorder w = Workorder.NewItem(WorkorderTypes.PreventiveMaintenance);
//Keep track if this is an age based pm
int nAgeMonths = rdr.GetInt32(18);
int nAgeYears = rdr.GetInt32(19);
bool bAged = false;
if (nAgeMonths != 0 || nAgeYears != 0)
bAged = true;
//------------------------
//If it's a rental unit that's ok because in
//units block a client would have been added with id 0 for self owned units
//rental units will return client id 0 which will match the special
//client in the hashtable htClients that was added during unit import
w.ClientID = (Guid)htClients[rdr.GetInt32(17)];
htPM.Add(rdr.GetInt32(0), w.ID);
w.Onsite = rdr.GetBoolean(14);
//Project
if (htProjects.Contains(rdr.GetInt32(13)))
w.ProjectID = (Guid)htProjects[rdr.GetInt32(13)];
//w.RegionID=new Guid("8236E8D1-CAB1-4797-9C34-93861954AE6A");//Default region
w.Summary = GetString(rdr, 2);
if (bAged)
{
w.Summary = "*Imported inactive (was unit age pm)* " + w.Summary;
}
//Category (type)
if (htCategories.Contains(rdr.GetInt32(12)))
w.WorkorderCategoryID = (Guid)htCategories[rdr.GetInt32(12)];
WorkorderItem wi = w.WorkorderItems[0];
wi.Summary = w.Summary;
wi.TechNotes = GetString(rdr, 3);
wi.TypeID = Guid.Empty;
w.WorkorderPreventiveMaintenance.Active = rdr.GetBoolean(1);
//however..if it's age based then it's inactive by default
if (bAged)
w.WorkorderPreventiveMaintenance.Active = false;
WorkorderItemScheduledUser wisu = wi.ScheduledUsers.Add(wi);
if (htUsers.Contains(rdr.GetInt32(11)))
wisu.UserID = (Guid)htUsers[rdr.GetInt32(11)];
//Get the start date from next service date
//get the start time from startime and end time from endtime
//munge together, add a little salt and wala!
//START
System.DateTime nsd = GetSmartDate(rdr, 6).Date;
System.DateTime nst = GetSmartDate(rdr, 15).Date;
System.DateTime nsdstart = new DateTime(nsd.Year, nsd.Month, nsd.Day, nst.Hour, nst.Minute, 0);
wisu.StartDate = new SmartDate(nsdstart).DBValue;
//STOP
System.DateTime nststop = GetSmartDate(rdr, 16).Date;
System.DateTime nsdstop = new DateTime(nsd.Year, nsd.Month, nsd.Day, nststop.Hour, nststop.Minute + 1, 0);
wisu.StopDate = new SmartDate(nsdstop).DBValue;
int nID = rdr.GetInt32(10);
switch (nID)
{
case 0://ANY DAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.AnyDayOfWeek;
break;
case 1://MONDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Monday;
break;
case 2://TUESDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Tuesday;
break;
case 31://WEDNESDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Wednesday;
break;
case 4://THURSDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Thursday;
break;
case 5://FRIDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Friday;
break;
case 6://SATURDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Saturday;
break;
case 7://SUNDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Sunday;
break;
}
//**** ASSUMPTION HERE: in an age based pm there may or may not be any values enterd
//for the repeating factor, so the code below *should* end up in a default value
//set in pm anyway
//calculate time span
//if there are any days then it's all in days
//if there are no days but weeks then it's all in days
//if there are no week or days then it's all in months
//In the new AyaNova only hours days months or years are valid timespans
if (rdr.GetInt32(9) != 0)//Days
{
//Set to days plus months *29.5 + weeks * 7 converted to int32
w.WorkorderPreventiveMaintenance.GenerateSpan = System.Convert.ToInt32(rdr.GetInt32(9) + (rdr.GetInt32(7) * 29.5) + (rdr.GetInt32(8) * 7));//whole days only so convert to int
w.WorkorderPreventiveMaintenance.GenerateSpanUnit = AyaUnitsOfTime.Days;
}
else if (rdr.GetInt32(8) != 0)//Weeks
{
//Convert the weeks and months to days
//An average month is 29.5 days long, but we can only handle ints
//so multiply month by 29.5 and convert to int32
w.WorkorderPreventiveMaintenance.GenerateSpan = System.Convert.ToInt32((rdr.GetInt32(7) * 29.5) + (rdr.GetInt32(8) * 7));
w.WorkorderPreventiveMaintenance.GenerateSpanUnit = AyaUnitsOfTime.Days;
}
else if (rdr.GetInt32(7) != 0)//Months
{
if (rdr.GetInt32(7) == 12)
{
w.WorkorderPreventiveMaintenance.GenerateSpanUnit = AyaUnitsOfTime.Years;
w.WorkorderPreventiveMaintenance.GenerateSpan = 1;
}
else
{
w.WorkorderPreventiveMaintenance.GenerateSpanUnit = AyaUnitsOfTime.Months;
w.WorkorderPreventiveMaintenance.GenerateSpan = rdr.GetInt32(7);
}
}
else
{
//apparently no time frame was set, it's likely age based with no repeat factor so
//set it to inactive to be on the safe side
w.WorkorderPreventiveMaintenance.Active = false;
}
w.WorkorderPreventiveMaintenance.ThresholdSpan = System.Convert.ToInt32(rdr.GetInt32(5));
w.WorkorderPreventiveMaintenance.ThresholdSpanUnit = AyaUnitsOfTime.Days;
if (htWOStatus.Contains(nNewWorkorderStatus))
w.WorkorderPreventiveMaintenance.WorkorderStatusID = (Guid)htWOStatus[nNewWorkorderStatus];
w.WorkorderPreventiveMaintenance.NextServiceDate = GetSmartDate(rdr, 6).DBValue;
//UNIT
if (htUnits.Contains(rdr.GetInt32(4)))
wi.UnitID = (Guid)htUnits[rdr.GetInt32(4)];
//-------------------------
w.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Tasks
ShowProgress("Imported: " + lImported.ToString() + " Preventive Maintenance records for UNIT (TIME / AGE BASED) records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region Import Preventive maintenance UNITS (METER BASED)
//PMHEAD.TYPE=1
//*********************************************************
ShowProgress("", "Importing Preventive maintenance UNITS (METER BASED)...", 0, out cancel);
cmd.CommandText = "SELECT Count(pmhead.id) AS CountOfid FROM pmhead INNER " +
"JOIN units ON pmhead.link = units.id WHERE (((pmhead.isclient)=False) AND ((pmhead.ismodel)=False) " +
"AND ((pmhead.autowo)=True) " +
"AND ((pmhead.type)=1));";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
cmd.CommandText = "SELECT " +
"pmhead.id, pmhead.active, pmhead.description, " +
"pmhead.notes, pmhead.link, pmhead.woconvert, pmhead.nextsrvmeter, " +
"pmhead.repeatevery AS METERCOUNTS, pmhead.rptweeks, " +
"pmhead.rptdays, pmhead.dow, pmhead.tech, " +
"pmhead.wotype, pmhead.woproject, pmhead.woonsite, " +
"pmhead.starttime, pmhead.endtime, units.client FROM pmhead " +
"INNER JOIN units ON pmhead.link = units.id WHERE " +
"(((pmhead.isclient)=False) AND ((pmhead.autowo)=True) AND ((pmhead.type)=1) AND ((pmhead.ismodel)=False));";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
Workorder w = Workorder.NewItem(WorkorderTypes.PreventiveMaintenance);
//------------------------
//If it's a rental unit that's ok because in
//units block a client would have been added with id 0 for self owned units
//rental units will return client id 0 which will match the special
//client in the hashtable htClients that was added during unit import
w.ClientID = (Guid)htClients[rdr.GetInt32(17)];
htPM.Add(rdr.GetInt32(0), w.ID);
w.Onsite = rdr.GetBoolean(14);
//Project
if (htProjects.Contains(rdr.GetInt32(13)))
w.ProjectID = (Guid)htProjects[rdr.GetInt32(13)];
//w.RegionID=new Guid("8236E8D1-CAB1-4797-9C34-93861954AE6A");//Default region
w.Summary = "*Imported inactive (was unit meter pm)* " + GetString(rdr, 2);
//Category (type)
if (htCategories.Contains(rdr.GetInt32(12)))
w.WorkorderCategoryID = (Guid)htCategories[rdr.GetInt32(12)];
WorkorderItem wi = w.WorkorderItems[0];
wi.Summary = w.Summary;
wi.TechNotes = GetString(rdr, 3);
wi.TypeID = Guid.Empty;
//meter based are always inactive
w.WorkorderPreventiveMaintenance.Active = false;
WorkorderItemScheduledUser wisu = wi.ScheduledUsers.Add(wi);
if (htUsers.Contains(rdr.GetInt32(11)))
wisu.UserID = (Guid)htUsers[rdr.GetInt32(11)];
//Get the start date from next service date
//get the start time from startime and end time from endtime
//munge together, add a little salt and wala!
//START
// System.DateTime nsd=DateTime.Today;
// System.DateTime nst=GetSmartDate(rdr,15).Date;
// System.DateTime nsdstart=new DateTime(nsd.Year,nsd.Month,nsd.Day,nst.Hour,nst.Minute,0);
wisu.StartDate = new SmartDate(DateTime.Now).DBValue;
//STOP
//System.DateTime nststop=DateTime.Today;
//System.DateTime nsdstop=new DateTime(nsd.Year,nsd.Month,nsd.Day,nststop.Hour,nststop.Minute+1,0);
wisu.StopDate = new SmartDate(DateTime.Now.AddHours(1)).DBValue;
int nID = rdr.GetInt32(10);
switch (nID)
{
case 0://ANY DAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.AnyDayOfWeek;
break;
case 1://MONDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Monday;
break;
case 2://TUESDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Tuesday;
break;
case 31://WEDNESDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Wednesday;
break;
case 4://THURSDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Thursday;
break;
case 5://FRIDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Friday;
break;
case 6://SATURDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Saturday;
break;
case 7://SUNDAY
w.WorkorderPreventiveMaintenance.DayOfTheWeek = AyaDayOfWeek.Sunday;
break;
}
//**** ASSUMPTION HERE: in an age based pm there may or may not be any values enterd
//for the repeating factor, so the code below *should* end up in a default value
//set in pm anyway
//calculate time span
//if there are any days then it's all in days
//if there are no days but weeks then it's all in days
//if there are no week or days then it's all in months
//In the new AyaNova only hours days months or years are valid timespans
//
// if(rdr.GetInt32(9)!=0)//Days
// {
// //Set to days plus months *29.5 + weeks * 7 converted to int32
// w.WorkorderPreventiveMaintenance.GenerateSpan=System.Convert.ToInt32(rdr.GetInt32(9)+(rdr.GetInt32(7)*29.5)+(rdr.GetInt32(8)*7));//whole days only so convert to int
// w.WorkorderPreventiveMaintenance.GenerateSpanUnit=AyaUnitsOfTime.Days;
// }
// else if(rdr.GetInt32(8)!=0)//Weeks
// {
// //Convert the weeks and months to days
// //An average month is 29.5 days long, but we can only handle ints
// //so multiply month by 29.5 and convert to int32
// w.WorkorderPreventiveMaintenance.GenerateSpan=System.Convert.ToInt32((rdr.GetInt32(7)*29.5)+(rdr.GetInt32(8)*7));
// w.WorkorderPreventiveMaintenance.GenerateSpanUnit=AyaUnitsOfTime.Days;
// }
// else if(rdr.GetInt32(7)!=0)//Months
// {
// if(rdr.GetInt32(7)==12)
// {
// w.WorkorderPreventiveMaintenance.GenerateSpanUnit=AyaUnitsOfTime.Years;
// w.WorkorderPreventiveMaintenance.GenerateSpan=1;
// }
// else
// {
// w.WorkorderPreventiveMaintenance.GenerateSpanUnit=AyaUnitsOfTime.Months;
// w.WorkorderPreventiveMaintenance.GenerateSpan=rdr.GetInt32(7);
// }
//
// }
// else
// {
// //apparently no time frame was set, it's likely age based with no repeat factor so
// //set it to inactive to be on the safe side
// w.WorkorderPreventiveMaintenance.Active=false;
// }
//
//
//
// w.WorkorderPreventiveMaintenance.ThresholdSpan=System.Convert.ToInt32(rdr.GetInt32(5));
// w.WorkorderPreventiveMaintenance.ThresholdSpanUnit=AyaUnitsOfTime.Days;
//
//
// if(htWOStatus.Contains(nNewWorkorderStatus))
// w.WorkorderPreventiveMaintenance.WorkorderStatusID=(Guid)htWOStatus[nNewWorkorderStatus];
//
// w.WorkorderPreventiveMaintenance.NextServiceDate=GetSmartDate(rdr,6).DBValue;
//
//UNIT
if (htUnits.Contains(rdr.GetInt32(4)))
wi.UnitID = (Guid)htUnits[rdr.GetInt32(4)];
//-------------------------
//w.WorkorderPreventiveMaintenance.NextServiceDate=System.DateTime.Now;
w.Save();
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Tasks
ShowProgress("Imported: " + lImported.ToString() + " Preventive Maintenance records for UNIT (METER BASED) records.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
#region PM Parts
//*********************************************************
// Mails
ShowProgress("", "Importing PM part selections...", 0, out cancel);
cmd.CommandText = "SELECT Count(pmparts.pmlink) AS CountOfpmlink FROM pmparts;";
rdr = cmd.ExecuteReader();
rdr.Read();
lTotalRecords = rdr.GetInt32(0);
rdr.Close(); if (cancel) goto BAIL;
cmd.CommandText = "SELECT pmparts.pmlink, pmparts.partnum, pmparts.quantity FROM pmparts ORDER BY pmparts.pmlink;";
rdr = cmd.ExecuteReader();
while (rdr.Read() && !cancel)
{
ShowProgress("", "", (int)((lImported / lTotalRecords) * 100), out cancel);
sLastOp = rdr.GetInt32(0).ToString();
//If there is a workorder and part matching that was already
//imported then add the parts to the workorder
if (htPM.Contains(rdr.GetInt32(0)) && htParts.Contains(rdr.GetInt32(1)))
{
Workorder w = Workorder.GetItem((Guid)htPM[rdr.GetInt32(0)]);
WorkorderItem wi = w.WorkorderItems[0];
WorkorderItemPart p = wi.Parts.Add(wi);
p.PartID = (Guid)htParts[rdr.GetInt32(1)];
p.Quantity = (decimal)rdr.GetFloat(2);
w.Save();
}
lImported++;
}
rdr.Close(); if (cancel) goto BAIL;
//END Mails
ShowProgress("Imported: " + lImported.ToString() + " PM part selections.", "", 100, out cancel);
lImported = 0;
lTotalRecords = 0;
//************************************************************************
#endregion
TimeSpan span = System.DateTime.Now.Subtract(dtStart);
ShowProgress("", "Import complete (" + span.TotalMinutes.ToString("F") + " minutes)", 100, out cancel);
ShowProgress("\r\n-IMPORT COMPLETED\r\n************************************\r\n", "", 100, out cancel);
return;
BAIL:
ShowProgress("\r\n-IMPORT CANCELLED!!\r\n****************************************************\r\n", "", -1, out cancel);
}
catch (Exception ex)
{
if (ex.InnerException == null)
ShowProgress("\r\n************************\r\n-IMPORT FAILED:\r\nLastOp: " + sLastOp + "\r\n" + ex.Message + "\r\n" + ex.StackTrace, "", -1, out cancel);
else
ShowProgress(
"\r\n************************\r\n" +
"-IMPORT FAILED:\r\n" +
"LastOp: " + sLastOp + "\r\n" +
ex.Message + "\r\n" +
ex.StackTrace + "\r\n\r\n" +
ex.InnerException.Message + "\r\n" +
ex.InnerException.StackTrace,
"", -1, out cancel);
}
finally
{
conn.Close();
}
//*************************
}
/// <summary>
/// Set AyaNova right
/// </summary>
/// <param name="nRight">Old right</param>
/// <returns></returns>
public void SetRight(int nRight, string sRightName, SecurityGroup s)
{
//Old rights were:
//0=not allowed , 1=full, 2=read only
//New rights are:
//NoAccess = 1, ReadOnly = 2, ReadWrite = 3, ReadWriteDelete = 4
UserRight ur = s.Rights[sRightName];
switch (nRight)
{
case 0:
ur.SecurityLevel = SecurityLevelTypes.NoAccess;
break;
case 1:
ur.SecurityLevel = SecurityLevelTypes.ReadWriteDelete;
break;
case 2:
ur.SecurityLevel = SecurityLevelTypes.ReadOnly;
break;
default:
ur.SecurityLevel = SecurityLevelTypes.NoAccess;
break;
}
}
delegate void ShowProgressDelegate(string sStatusString, string sCurrentString, int nProgress, out bool cancel);
/// <summary>
/// Feedback to user through UI thread
/// </summary>
/// <param name="sStatusString"></param>
/// <param name="sCurrentString"></param>
/// <param name="nProgress"></param>
void ShowProgress(string sStatusString, string sCurrentString, int nProgress, out bool cancel)
{
// Make sure we're on the right thread
if (edStatus.InvokeRequired == false)
{
if (sStatusString != "")
{
sStatusString = sStatusString + "\r\n";
edStatus.AppendText(sStatusString);
}
if (sStatusString.LastIndexOf("-IMPORT ") != -1)
{
//Import has completed,disable import cancel button
btnStopSearch.Visible = false;
}
if (nProgress != -1)
pBar.Value = nProgress;
if (sCurrentString != "")
lblCurrentOp.Text = sCurrentString;
// Check for Cancel
cancel = (_state == OpState.Canceled);
// Check for completion
if (cancel)
{
_state = OpState.Pending;
this.btnStopSearch.Visible = false;
}
}
else
{
// Show progress asynchronously
// ShowProgressDelegate showProgress =
// new ShowProgressDelegate(ShowProgress);
// BeginInvoke(showProgress,
// new object[] { sStatusString, sCurrentString, nProgress});
ShowProgressDelegate showProgress = new ShowProgressDelegate(ShowProgress);
object inoutCancel = false; // Avoid boxing and losing our return value
// Show progress synchronously (so we can check for cancel)
Invoke(showProgress, new object[] { sStatusString, sCurrentString, nProgress, inoutCancel });
cancel = (bool)inoutCancel;
}
}
// private void Status(string sStringToAdd)
// {
// sStringToAdd=sStringToAdd+"\r\n";
// edStatus.AppendText(sStringToAdd);
//
// )
//
//}
/// <summary>
/// provides a way to get a string while handling null values
/// </summary>
/// <param name="rdr"></param>
/// <param name="nColumn"></param>
/// <returns></returns>
private string GetString(OleDbDataReader rdr, int nColumn)
{
if (rdr.IsDBNull(nColumn))
return "";
else//Important, some noobs have imported lot's of data with spaces in field names or from
//sources with fixed length fields where there is a lot of padding, the Trim will
//ensure that they don't get imported unnecessarily
return rdr.GetString(nColumn).Trim();
}
/// <summary>
/// provides a way to get a string while handling null values
/// with max chars returned
/// </summary>
/// <param name="rdr"></param>
/// <param name="nColumn"></param>
/// <returns></returns>
private string GetString(OleDbDataReader rdr, int nColumn, int maxChars)
{
if (rdr.IsDBNull(nColumn))
return "";
else
{
string s = rdr.GetString(nColumn).Trim();//while working on case 1209 noticed trim missing here as in above
if (s.Length > maxChars) return s.Substring(0, maxChars);
return s;
}
}
/// <summary>
/// provides a way to get a Smart Date while handling null values
/// </summary>
/// <param name="rdr"></param>
/// <param name="nColumn"></param>
/// <returns></returns>
private CSLA.SmartDate GetSmartDate(OleDbDataReader rdr, int nColumn)
{
if (rdr.IsDBNull(nColumn))
return new CSLA.SmartDate();
else
{
CSLA.SmartDate sd = new CSLA.SmartDate(rdr.GetDateTime(nColumn));
//date sanity check
//If the date is out of whack make a set date to use instead
//this ensures sql server won't bomb with:
//SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
if (sd.Date.Year < 1900 || sd.Date.Year > 3000)
{
return new CSLA.SmartDate(new System.DateTime(1988, 8, 8, 8, 8, 8));
}
else
return sd;
}
}
/// <summary>
/// provides a way to get a decimal (currency) while handling null values
/// </summary>
/// <param name="rdr"></param>
/// <param name="nColumn"></param>
/// <returns></returns>
private decimal GetDecimal(OleDbDataReader rdr, int nColumn)
{
if (rdr.IsDBNull(nColumn))
return 0;
else
return rdr.GetDecimal(nColumn);
}
/// <summary>
/// provides a way to get an INT while handling null values
/// </summary>
/// <param name="rdr"></param>
/// <param name="nColumn"></param>
/// <returns></returns>
private int SafeGetInt32(OleDbDataReader rdr, int nColumn)
{
if (rdr.IsDBNull(nColumn))
return 0;
else
return rdr.GetInt32(nColumn);
}
#endregion
#region Authentication
private bool DoLogin()
{
Login dlg = new Login();
dlg.ShowDialog(this);
if (dlg.LoggingIn)
{
if (DoNewLogin(dlg.Username, dlg.Password))
{
if (DoOldLogin(dlg.OldUsername, dlg.OldPassword))
{
return true;
}
else
{
MessageBox.Show("The old AyaNova CE username or password are not valid", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show("The new AyaNova username or password are not valid", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
DoLogout();
return false;
}
/// <summary>
/// Login to old AyaNova CE database
/// </summary>
/// <param name="User"></param>
/// <param name="Password"></param>
/// <returns></returns>
private bool DoOldLogin(string User, string Password)
{
bool bLoginResult = false;
string userhashed = "";
string passwordhashed = "";
//Get hash of user
userhashed = GZHash(User).ToString();
if (Password == "")
Password = "@" + userhashed + "gz";
else
passwordhashed = GZHash(Password).ToString();
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source= " + strOldDBPath;
try
{
//****************************
string q = string.Format("SELECT users.c FROM users WHERE (((users.login)='{0}') AND ((users.pass)='{1}'));",
userhashed, passwordhashed);
conn.Open();
OleDbCommand cmd = new OleDbCommand(q, conn);
OleDbDataReader rdr;
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr.GetInt32(0) != 1)
{
MessageBox.Show("Only users who are memebers of the <MANAGERS> security group can import from AyaNova CE.\r\nYou must log on with the manager or equivalent account.");
bLoginResult = false;
}
else
bLoginResult = true;
}
rdr.Close();
//****************************
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
conn.Close();
}
//************************************
return bLoginResult;
}
/// <summary>
/// Calculate AyaNova CE style hash
///
/// </summary>
/// <param name="src"></param>
/// <returns></returns>
private uint GZHash(string src)
{
// Convert the string into a unicode byte[].
Encoding unicode = Encoding.Unicode;
byte[] unicodeBytes = unicode.GetBytes(src);
//Calculate the hash
uint hash = 5381;
byte c;
//AyaNova CE knew nothing about Unicode so throw out every second byte in the array
//gives the equivalent of the original char* pointer based function
for (int x = 0; x < unicodeBytes.Length; x += 2)
{
c = unicodeBytes[x];
hash = ((hash << 5) + hash) + c;
}
return hash;
}
/// <summary>
/// Login to new AyaNova database
/// </summary>
/// <param name="User"></param>
/// <param name="Password"></param>
/// <returns></returns>
private bool DoNewLogin(string Login, string Password)
{
if (Thread.CurrentPrincipal.Identity == null || !Thread.CurrentPrincipal.Identity.IsAuthenticated)
AyaBizUtils.Login(Login, Password);
if (Thread.CurrentPrincipal.Identity.IsAuthenticated)
{
//To prevent potential freeze during import when
//global is fetched during a save of an object being imported
//to break it's text global must be pre-initialized this way
GZTW.AyaNova.BLL.AyaBizUtils.mGlobalSettings = Global.GetItem();
if (!User.IsAdmin)
{
MessageBox.Show("Only the new AyaNova Admin account can import from AyaNova CE.\r\nYou must log on with the admin account to the new AyaNova.");
DoLogout();
return false;
}
//Proceed with import
return true;
}
else
{
DoLogout();
return false;
}
}
private void DoLogout()
{
Thread.CurrentPrincipal = null;
}
#endregion
}//End of class
#region Helper classes
public class AyaNovaDBItem
{
private string path;
private string regto;
private System.DateTime databaseLastModified;
private System.DateTime workorderLastCreated;
private long highestWorkorderNumber;
//if bControlItem then path contains
//the command to run, not the path of the db
private bool bControlItem;
public AyaNovaDBItem(string strpath, string strregto,
System.DateTime dtDatabaseLastModified, System.DateTime dtWorkorderLastCreated,
long lHighestWorkorderNumber)
{
this.path = strpath;
this.regto = strregto;
this.databaseLastModified = dtDatabaseLastModified;
this.workorderLastCreated = dtWorkorderLastCreated;
this.highestWorkorderNumber = lHighestWorkorderNumber;
this.bControlItem = false;
}
public AyaNovaDBItem(string strpath)
{
this.path = strpath;
this.bControlItem = true;
}
public string Path
{
get
{
return path;
}
}
public string RegTo
{
get
{
return regto;
}
}
public string DatabaseLastModified
{
get
{
return databaseLastModified.ToString();
}
}
public string WorkorderLastCreated
{
get
{
return workorderLastCreated.ToString();
}
}
public string HighestWorkorderNumber
{
get
{
return highestWorkorderNumber.ToString();
}
}
public bool ControlItem
{
get
{
return bControlItem;
}
}
}
#endregion
#region Upgrade CE
public static class UpdateCEDatabaseSchema
{
//FUTURE READER keywords to find this method
//update database schema 171 1.9 scdata.sc ayanova ce
//etc etc etc
private static OleDbCommand cmd = null;
private static void executeQuery(string q)
{
cmd.CommandText = q; cmd.ExecuteNonQuery();
}
public static void go(System.Data.OleDb.OleDbConnection conn, int nVersion)
{
cmd = new OleDbCommand();
cmd.Connection = conn;
//string msg,strTemp,strData;
string q, q2;
DateTime dtDefault = new DateTime(1968, 03, 12);
bool buptodate = false;
bool bUpdateRanOK;
//do whatever updates are necessary.
while (!buptodate)
{
switch (nVersion)
{
#region older versions I didn't need to migrate (yet)
// //==================================================
//case 126://changed to 127 on 8/22/00
// {
// //Added a record to the rptsmaster table for the
// //workorder dispatch report
// //Not a schema update only an added record
// //see if the record is here already so it's not done twice:
// rs->Query("SELECT rptsmaster.virtualname "
// "FROM rptsmaster WHERE (((rptsmaster.virtualname)=\"Workorder dispatch\"));");
// if(rs->IsEmpty())
// {
// executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
// "SELECT \"Workorder dispatch\", \"wodispst.rpt\", \"wodispatch\", False, 32767, 21;");
// }
// //fix the version number up
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"127\";");
// nVersion=127;
// }
// break;
// //======================================================
//case 127://changed to 128 on 8/24/00
// {
// //Added a record to the rptsmaster table for the
// //QUICK workorder dispatch report
// //Not a schema update only an added record
// //see if the record is here already so it's not done twice:
// rs->Query("SELECT rptsmaster.virtualname "
// "FROM rptsmaster WHERE (((rptsmaster.virtualname)=\"Workorder dispatch (Quick)\"));");
// if(rs->IsEmpty())
// {
// executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
// "SELECT \"Workorder dispatch (Quick)\", \"wodispqu.rpt\", \"wodispatch\", False, 32767, 22;");
// }
// //fix the version number up
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"128\";");
// nVersion=128;
// }
// break;
// //=====================================================
//case 128://changed to 129 on 8/28/00
// {
// //Dropped contacts table fields: wolink, problink
// //added fields: clientlink
// executeQuery("ALTER TABLE contacts DROP COLUMN wolink;");
// executeQuery("ALTER TABLE contacts DROP COLUMN problink;");
// executeQuery("ALTER TABLE contacts ADD COLUMN clientlink LONG;");
// //fix the version number up
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"129\";");
// nVersion=129;
// }
// break;
//case 129://changed to 130 on 8/29/00 - 8/31/00
// {
// //contacts table added field:indexed
// //units table added field:indexed
// executeQuery("ALTER TABLE contacts ADD COLUMN indexed YESNO;");
// executeQuery("UPDATE contacts SET contacts.indexed = False;");
// executeQuery("ALTER TABLE units ADD COLUMN indexed YESNO;");
// executeQuery("UPDATE units SET units.indexed = False;");
// //add the unit history to reports screen
// q2="SELECT \"user\" AS zCurrentUser, probs.id, Format([start],\"Short Date\") AS "
// "servdate, labor.details, IIf(IsNull([company]),[clients]![last] & \", \" & [clients]![first],[company]) "
// "AS clientcompany, units.sn, unitmodels.model, users.initials, probs.meter, ztotalparts.totalparts, "
// "ztotallabor.totallabor "
// "FROM [SELECT Sum((labor.hours*rates.rate)+(labor.travhours*travrates.rate)) AS totallabor, "
// "probs.id FROM probs INNER JOIN (rates INNER JOIN (rates AS travrates INNER JOIN labor ON "
// "travrates.id = labor.rate) ON rates.id = labor.rate) ON probs.id = labor.link GROUP BY probs.id]. AS "
// "ztotallabor RIGHT JOIN ([SELECT IIf(IsNull(Sum(woparts.quantity*woparts.price)),0, "
// "Sum(woparts.quantity*woparts.price)) AS totalparts, probs.id FROM probs LEFT JOIN woparts "
// "ON probs.id = woparts.link GROUP BY probs.id]. AS ztotalparts RIGHT JOIN (users RIGHT JOIN "
// "((labor RIGHT JOIN (((wo RIGHT JOIN probs ON wo.id = probs.wolink) LEFT JOIN units ON "
// "probs.unit = units.id) LEFT JOIN unitmodels ON units.model = unitmodels.id) ON labor.link = "
// "probs.id) LEFT JOIN clients ON wo.client = clients.id) ON users.id = labor.tech) ON ztotalparts.id "
// "= probs.id) ON ztotallabor.id = probs.id "
// "WHERE (((probs.unit)~UNIT) AND ((units.sn) Is Not Null) AND ((labor.start) Between #~STRT# And #~END_#)) "
// "ORDER BY labor.start DESC;";
// q=string.Format("UPDATE rptsmaster SET rptsmaster.x = True, rptsmaster.criteriafields = 400, rptsmaster.query = '%s' "
// "WHERE (((rptsmaster.id)=7));",q2);
// executeQuery(q);
// //fix the version number up
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"130\";");
// nVersion=130;
// }
// break;
// //=====================================================
//case 130://changed to 131 on 9/12/00
// {
// //PM module additions
// executeQuery("ALTER TABLE pmschedules ADD COLUMN autoworkorder YESNO;");
// executeQuery("UPDATE pmschedules SET pmschedules.autoworkorder = False;");
// //fix the version number up
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"131\";");
// nVersion=131;
// }
// break;
// //***************************************************************************
//case 131://changed to 132 on 9/22/00 and now 133 on 10/02/00
// { //since it's unreleased, decided to bump up one more.
// //PM module additions
// //Add a last meter reading column to the units table
// //this is updated when someone enters a workorder unit count.
// //to make pm easier to deal with.
// executeQuery("ALTER TABLE units ADD COLUMN lastmeter INTEGER;");
// //NOTE: CANT SET TO DEFAULT OF ZERO WITHOUT IT CRASHING OUT
// //Delete the pmschedules, superseded by pmhead table below
// executeQuery("DROP TABLE pmschedules;");
// //new pm header table
// q=string.Format("CREATE TABLE pmhead ("
// "id COUNTER CONSTRAINT PK_pmhead PRIMARY KEY, "
// "description TEXT(50), "
// "notes MEMO, "
// "link INTEGER DEFAULT 0, "//link to client or unit id number
// "type INTEGER DEFAULT 0, " //0-timebased,1-meter,2-agebased
// "isclient YESNO DEFAULT False, " //true = client false=unitbased
// "ismodel YESNO DEFAULT False, " //true = MODEL false=units or clients
// "autowo YESNO DEFAULT False, "
// "autorpt YESNO DEFAULT False, "
// "dow INTEGER DEFAULT 0," //autoschedule day of week 0any 1-monday 2-tuesd etc
// "tech INTEGER DEFAULT 0, " //HEREpreferred technician
// "woconvert INTEGER DEFAULT 0, " //convert to workorder days / meter counts in advance
// "nextsrvdate DATETIME DEFAULT #%s#, "
// "nextsrvmeter INTEGER DEFAULT 0, "//next meter count to service on
// "repeatevery INTEGER DEFAULT 0, "//repeat every meter / months
// "agemonths INTEGER DEFAULT 0, "//Age based: months old to schedule
// "ageyears INTEGER DEFAULT 0, "//Age based: years old to schedule
// "active YESNO DEFAULT True, "//on hold or active?
// "series INTEGER DEFAULT 0, "//schedule this many series at a time
// "creator INTEGER DEFAULT 0, "//user who created record
// "created DATETIME DEFAULT #%s#, " //date created
// "modifier INTEGER DEFAULT 0, "//date modified who cares?? probably no one
// "modified DATETIME DEFAULT #%s#, "
// "wotype INTEGER DEFAULT 0, "
// "woquick YESNO DEFAULT False, "
// "woproject INTEGER DEFAULT 0, "
// "woonsite YESNO DEFAULT False);"
// ,dtDefault.Format(_T("%m/%d/%Y")),dtDefault.Format(_T("%m/%d/%Y")),dtDefault.Format(_T("%m/%d/%Y")));
// executeQuery(q);
// //new table to hold preventative maintenance items
// //in schedule viewer
// q=string.Format("CREATE TABLE pmitems ("
// "id COUNTER CONSTRAINT PK_pmitem PRIMARY KEY, "
// "pmschedlink INTEGER DEFAULT 0, "
// "clientlink INTEGER DEFAULT 0, "
// "schedate DATETIME DEFAULT #%s#, "
// "schedmeter INTEGER DEFAULT 0, "
// "autowo YESNO DEFAULT True, "
// "hold YESNO DEFAULT False, "
// "schedtech INTEGER DEFAULT 0, "
// "link INTEGER DEFAULT 0, "//link to client or unit id number
// "isclient YESNO DEFAULT False);",dtDefault.Format(_T("%m/%d/%Y"))); //true = client false=unitbased
// executeQuery(q);
// //clear out the pmparts table
// executeQuery("DELETE pmparts.* FROM pmparts;");
// //set correct report control profile for PM check screen now 8 columns was 7
// executeQuery("UPDATE users SET users.pmprofile = \"(8,8) 111 70 91 93 67 67 258 0 0 1 2 3 4 5 6 7\";");
// //Add the lastmaintdate column to the defaults table
// //this is used for maintenance to ensure that only one station
// //tries to do it at a time: When SC is run, it checks to see if
// //this date is older than the current date, if so, it "locks" it by
// //updating to today's date, thereby effectively locking out any latecomers
// //then runs any required maintenance.
// executeQuery("ALTER TABLE defaults ADD COLUMN lastmaintdate DATETIME;");
// q=string.Format("UPDATE defaults SET defaults.lastmaintdate = #%s#;",dtDefault.Format(_T("%m/%d/%Y")));
// executeQuery(q);
// //Add the woestimate column to the defaults table, this is to
// //flag whether to use estimates on dispatch workorders or not.
// //this is in response to TCCSI client that needs this and it
// //will now be available to all users. This flag is settable under the
// //defaults screen, program, not users
// executeQuery("ALTER TABLE defaults ADD COLUMN woestimate YESNO;");
// executeQuery("UPDATE defaults SET defaults.woestimate = False;");
// //Customer reference number and contact
// //as suggested by Rob MacKay in Australia
// executeQuery("ALTER TABLE wo ADD COLUMN clientrefnum TEXT(50);");
// executeQuery("ALTER TABLE wo ADD COLUMN clientcontact TEXT(50);");
// //Add a first boot field to detect new evaluation versions
// //this will be used to show a friendly welcome to message and
// //to fixup the dates in the demo data.
// //set to false because if this field isn't in the table already,
// //it means user has older version so it just needs to be there
// //not actually used
// executeQuery("ALTER TABLE defaults ADD COLUMN firstboot YESNO;");
// executeQuery("UPDATE defaults SET defaults.firstboot = False;");
// //refdate is used in an eval to fixup all the dates
// //in the evaluation database.
// //so that the evaluator sees the demo data as current
// executeQuery("ALTER TABLE defaults ADD COLUMN refdate DATETIME;");
// //DROP the pm columns no longer required in the units table
// executeQuery("ALTER TABLE units DROP COLUMN lastpmmetric;");
// executeQuery("ALTER TABLE units DROP COLUMN lastpmdate;");
// executeQuery("ALTER TABLE units DROP COLUMN nextpmmetric;");
// executeQuery("ALTER TABLE units DROP COLUMN nextpmdate;");
// //fix the version number up
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"133\";");
// nVersion=133;
// }
// break;
// //=====================================================
//case 133://changed to 134 for schedule view
// {
// CString strID,strName,strRights,strHash;
// long lData;
// GZK k;
// //Add new right:
// //loop through all the group records
// rs->Query("SELECT groups.* FROM groups;");
// do{
// rs->FetchField("id",&lData);
// rs->FetchField("a",&strName);
// rs->FetchField("b",&strRights);
// //decrypt the original rights string
// k.GZDecrypt(&strRights,false);
// strRights=strRights+"1";//add on the extra right for schedule view
// //re-'crypt'
// k.GZEncrypt(&strRights,false);
// //HASH
// strHash.Format("%u%s%s",lData,strName,strRights);
// k.GZHash(&strHash);
// //save the rights
// rs->UpdateField("b",&strRights);
// //save the hash
// rs->UpdateField("c",&strHash);
// //save the record
// if(!rs->SaveRecord())
// AfxMessageBox("Error trying to save record");
// }while(rs->MoveForward());
// executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
// "SELECT \"Parts\", \"parts.rpt\", \".\", False, 32767, 23;");
// //fix the version number up
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"134\";");
// nVersion=134;
// }
// break;
// //***************************************************************************
// //=====================================================
//case 134://changed to 135 on 12/19/00
// {
// executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
// "SELECT \"Search results\", \"srch.rpt\", \".\", False, 32767, 24;");
// executeQuery("ALTER TABLE users ADD COLUMN rpttext1 MEMO;");
// //fix the version number up
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"135\";");
// nVersion=135;
// }
// break;
// //=====================================================
//case 135://changed to 136 on Jan 15 2001
// {
// bUpdateRanOK=true;
// //USER RECORD CHANGES
// executeQuery("ALTER TABLE users ADD COLUMN defschedstarthour INTEGER;");
// executeQuery("ALTER TABLE users ADD COLUMN defschedstophour INTEGER;");
// executeQuery("ALTER TABLE users ALTER defschedstarthour SET DEFAULT '0';");
// executeQuery("ALTER TABLE users ALTER defschedstophour SET DEFAULT '0';");
// if(!executeQuery("UPDATE users SET users.defschedstarthour = 0, users.defschedstophour = 0;")) bUpdateRanOK=false;
// //WORKORDER COLUMN CHANGES
// executeQuery("ALTER TABLE wo ADD COLUMN status INTEGER;");
// executeQuery("ALTER TABLE wo ALTER status SET DEFAULT '0';");
// executeQuery("UPDATE wo SET wo.status = 0;");
// executeQuery("ALTER TABLE wo ADD COLUMN ourref TEXT(80);");
// executeQuery("ALTER TABLE wo ADD COLUMN esthours REAL;");
// executeQuery("ALTER TABLE wo ADD COLUMN estrate INTEGER;");
// executeQuery("ALTER TABLE wo ALTER notes TEXT(110)");
// //STATUS VIEW SCREEN CHANGE
// executeQuery("UPDATE statusviews SET statusviews.viewprofile = \"(17,17) 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16\";");
// //fix the version number up as long as updates seem ok
// if(bUpdateRanOK)
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"136\";");
// AfxMessageBox("Part of this update modifies the main workorder list view screen.\r\n\r\n"
// "You will need to re-order the columns in the main view to your preferences and re-save the views.\r\n\r\n"
// "This update affects the column order only, not the underlying restriction settings.");
// nVersion=136;
// }
// break;
// //=====================================================
//case 136://changed to 137 on Jan 18 2001
// {
// //NEW FIELDS TO ADD TO STATUS VIEW TABLE
// executeQuery("ALTER TABLE statusviews ADD COLUMN ourref INTEGER;");
// executeQuery("ALTER TABLE statusviews ADD COLUMN status INTEGER;");
// executeQuery("ALTER TABLE statusviews ALTER ourref SET DEFAULT '0';");
// executeQuery("ALTER TABLE statusviews ALTER status SET DEFAULT '0';");
// executeQuery("UPDATE statusviews SET statusviews.status = 1;");
// executeQuery("UPDATE statusviews SET statusviews.ourref = 1;");
// executeQuery("ALTER TABLE woparts ADD COLUMN sn TEXT(255);");
// //client account number
// executeQuery("ALTER TABLE clients ADD COLUMN acctnumber TEXT(40);");
// //fix the version number up as long as updates seem ok
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"137\";");
// nVersion=137;
// }
// break;
//case 137://changed to 138 on Jan 29 2001 -bug in tech billing summary
// {
// q2="SELECT [users].[last] & \", \" & [users].[first] AS name, Sum(techsubq.hours) AS SumOfhours, Sum(techsubq.nchours) AS SumOfnchours, Sum(techsubq.travhours) AS SumOftravhours, Sum(techsubq.wototal) AS SumOfwototal "
// "FROM [SELECT labor.tech, labor.hours, labor.nchours, labor.travhours, (rates.rate*labor.hours)+(travelrates.rate*labor.travhours) AS wototal "
// "FROM rates AS travelrates RIGHT JOIN (((labor LEFT JOIN (probs LEFT JOIN wo ON probs.wolink = wo.id) ON labor.link = probs.id) LEFT JOIN rates ON labor.rate = rates.id) LEFT JOIN clients ON wo.client = clients.id) ON travelrates.id = labor.travrate "
// "WHERE (((labor.tech)<>-1) AND ((labor.stop) Between #~STRT# And #~END_#) AND ((clients.czone)<>-1) AND ((wo.type)<>-1) AND ((wo.project)<>-1) AND ((wo.client)<>-1))]. AS techsubq INNER JOIN users ON techsubq.tech = users.id "
// "GROUP BY techsubq.tech, [users].[last] & \", \" & [users].[first] "
// "ORDER BY [users].[last] & \", \" & [users].[first];";
// q=string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
// "WHERE (((rptsmaster.id)=17));",q2);
// executeQuery(q);
// //fix the version number up as long as updates seem ok
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"138\";");
// nVersion=138;
// }
// break;
//case 138://changed to 139 on Jan 29 2001 -bug in tech billing summary and didn't fix it the first time
// {
// q2="SELECT [users].[last] & \", \" & [users].[first] AS name, Sum(techsubq.hours) AS SumOfhours, Sum(techsubq.nchours) AS SumOfnchours, Sum(techsubq.travhours) AS SumOftravhours, Sum(techsubq.wototal) AS SumOfwototal "
// "FROM [SELECT labor.tech, labor.hours, labor.nchours, labor.travhours, (rates.rate*labor.hours)+(travelrates.rate*labor.travhours) AS wototal "
// "FROM rates AS travelrates RIGHT JOIN (((labor LEFT JOIN (probs LEFT JOIN wo ON probs.wolink = wo.id) ON labor.link = probs.id) LEFT JOIN rates ON labor.rate = rates.id) LEFT JOIN clients ON wo.client = clients.id) ON travelrates.id = labor.travrate "
// "WHERE (((labor.tech)~TECH) AND ((labor.stop) Between #~STRT# And #~END_#) AND ((clients.czone)~ZONE) AND ((wo.type)~CAT_) AND ((wo.project)~PROJ) AND ((wo.client)~CUST))]. AS techsubq INNER JOIN users ON techsubq.tech = users.id "
// "GROUP BY techsubq.tech, [users].[last] & \", \" & [users].[first] "
// "ORDER BY [users].[last] & \", \" & [users].[first];";
// q=string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
// "WHERE (((rptsmaster.id)=17));",q2);
// executeQuery(q);
// //fix the version number up as long as updates seem ok
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"139\";");
// nVersion=139;
// }
// break;
//case 139://changed to 140 on Feb 15 2001 Added color to status items
// {
// executeQuery("ALTER TABLE probstat ADD COLUMN red INTEGER;");
// executeQuery("ALTER TABLE probstat ADD COLUMN green INTEGER;");
// executeQuery("ALTER TABLE probstat ADD COLUMN blue INTEGER;");
// executeQuery("ALTER TABLE probstat ALTER red SET DEFAULT '0';");
// executeQuery("ALTER TABLE probstat ALTER green SET DEFAULT '0';");
// executeQuery("ALTER TABLE probstat ALTER blue SET DEFAULT '0';");
// //set all to black initially
// executeQuery("UPDATE probstat SET probstat.red = 0;");
// executeQuery("UPDATE probstat SET probstat.green = 0;");
// executeQuery("UPDATE probstat SET probstat.blue = 0;");
// //fix the version number up as long as updates seem ok
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"140\";");
// nVersion=140;
// }
// break;
//case 140://changed to 141 on March 7th 2001 bug in report: Billing summary - hours/parts/3rd party
// {
// q2="SELECT IIf(IsNull([clients].[company]),[clients].[last] & \", \" & [clients].[first],[clients].[company]) AS company, Choose([typeUNIONsub].[type],\"Service\",\"3rd party\",\"Parts\") AS chargesfrom, Sum(typeUNIONsub.total) AS total "
// "FROM ([SELECT probs.wolink AS link, \"1\" AS type, (rates.rate*labor.hours)+(travelrates.rate*labor.travhours) AS total "
// "FROM (rates INNER JOIN (rates AS travelrates INNER JOIN labor ON travelrates.id = labor.travrate) ON rates.id = labor.rate) INNER JOIN (clients INNER JOIN (wo INNER JOIN probs ON wo.id = probs.wolink) ON clients.id = wo.client) ON labor.link = probs.id "
// "WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((clients.czone)~ZONE) AND ((wo.client)~CUST) AND ((wo.type)~CAT_) AND ((wo.project)~PROJ)) "
// "UNION ALL (SELECT probs.wolink AS link, \"2\" AS type, subrepair.charge AS total "
// "FROM subrepair INNER JOIN (clients INNER JOIN (wo INNER JOIN probs ON wo.id = probs.wolink) ON clients.id = wo.client) ON subrepair.link = probs.id "
// "WHERE (((subrepair.charge)<>0) AND ((wo.created) Between #~STRT# And #~END_#) AND ((clients.czone)~ZONE) AND ((wo.client)~CUST) AND ((wo.type)~CAT_) AND ((wo.project)~PROJ)) "
// "UNION ALL (SELECT probs.wolink, \"3\" AS type, woparts.quantity*woparts.price AS total "
// "FROM woparts INNER JOIN (clients INNER JOIN (wo INNER JOIN probs ON wo.id = probs.wolink) ON clients.id = wo.client) ON woparts.link = probs.id "
// "WHERE (((clients.czone)~ZONE) AND ((wo.created) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((wo.type)~CAT_) AND ((wo.project)~PROJ))) "
// " )]. AS typeUNIONsub INNER JOIN wo ON typeUNIONsub.link = wo.id) INNER JOIN clients ON wo.client = clients.id "
// "GROUP BY IIf(IsNull([clients].[company]),[clients].[last] & \", \" & [clients].[first],[clients].[company]), Choose([typeUNIONsub].[type],\"Service\",\"3rd party\",\"Parts\");";
// q=string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
// "WHERE (((rptsmaster.id)=4));",q2);
// executeQuery(q);
// //fix the version number up as long as updates seem ok
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"141\";");
// nVersion=141;
// }
// break;
//case 141://changed to 142 on March 26th 2001 various updates
// {
// //Add zone field to users
// executeQuery("ALTER TABLE users ADD COLUMN defzone INTEGER;");
// executeQuery("ALTER TABLE users ALTER defzone SET DEFAULT '0';");
// //set all zones to 0 initially
// executeQuery("UPDATE users SET users.defzone = 0;");
// //Add default wo status fields to defaults
// executeQuery("ALTER TABLE defaults ADD COLUMN wonewstat INTEGER;");
// executeQuery("ALTER TABLE defaults ADD COLUMN woclosestat INTEGER;");
// executeQuery("ALTER TABLE defaults ADD COLUMN woreopenstat INTEGER;");
// //add default schedule screen refresh time period
// executeQuery("ALTER TABLE defaults ADD COLUMN schedrfrshsecs INTEGER;");
// //set all 0 initially
// executeQuery("UPDATE defaults SET defaults.wonewstat = 0, defaults.woclosestat = 0, defaults.woreopenstat = 0, defaults.schedrfrshsecs = 60;");
// //fix the version number up as long as updates seem ok
// executeQuery("UPDATE defaults SET defaults.versioninfo = \"142\";");
// nVersion=142;
// }
// break;
#endregion older versions I didn't need to migrate yet
case 142://changed to 143 on April 6th 2001 various updates
{
//Add default wo status fields to defaults
executeQuery("ALTER TABLE defaults ADD COLUMN schedwindow INTEGER;");
//set to 30 day window by default
executeQuery("UPDATE defaults SET defaults.schedwindow = 30;");
//fix the version number up as long as updates seem ok
executeQuery("UPDATE defaults SET defaults.versioninfo = \"143\";");
nVersion = 143;
}
break;
case 143://changed to 144 on May 8th 2001 various updates
{
//Add xfertempl table and set values. This is for import export
q = ("CREATE TABLE xfertempl ("
+ "name TEXT(80) CONSTRAINT PK_name PRIMARY KEY, "
+ "import YESNO DEFAULT False, "
+ "delimiter TEXT(1), "
+ "fields MEMO, "
+ "builtin YESNO DEFAULT False, "
+ "fieldnames YESNO DEFAULT False, "
+ "path MEMO, "
+ "[sql] MEMO, "
+ "[table] TEXT(50));");
executeQuery(q);
// executeQuery(q);
////rs->Close();
q2 = "\"+first\",\"+last\",\"+company\",\"+mailaddress\",\"+streetaddress\",\"+city\",\"+stateprov\",\"+postal\",\"+country\",\"+bizphone\",\"+extension\",\"+fax\",\"+email\",\"+technotes\",\"+alert\",\"+generalnotes\",\"-acctnumber\"";
q = string.Format("INSERT INTO xfertempl ( name, import, delimiter, fields, builtin, fieldnames, path, [sql], [table] ) "
+ "SELECT \"Clients\" , False, \",\",'%s', True, True, \"c:\\\", \"SELECT * FROM clients;\",\"clients\";", q2);
cmd.CommandText = q;
cmd.ExecuteNonQuery();
q2 = "\"+partnumber\",\"+description\",\"+avgcost\",\"+retail\",\"+notes\",\"+supplierpart\",\"-upc\"";
q = string.Format("INSERT INTO xfertempl ( name, import, delimiter, fields, builtin, fieldnames, path, [sql], [table] ) "
+ "SELECT \"Parts\" , False, \",\",'%s', True, True, \"c:\\\", \"SELECT * FROM parts;\",\"parts\";", q2);
executeQuery(q);
q2 = "\"+Customer\",\"+Contact\",\"+Phone\",\"+Fax\",\"+Address\",\"+Notes\",\"+Account\"";
q = string.Format("INSERT INTO xfertempl ( name, import, delimiter, fields, builtin, fieldnames, path, [sql], [table] ) "
+ "SELECT \"Quick Books - Clients\" , True, \"T\",'%s', True, False, \"c:\\\", \"\",\"QBCLIENTS\";", q2);
executeQuery(q);
q2 = "\"+id\",\"+sn\",\"+id1\",\"+id2\",\"+id3\",\"+company\",\"+first\",\"+last\",\"+receipt\",\"+purchasedate\",\"+model\",\"+manufacturer\",\"+name\",\"+loaner\",\"+lastmeter\"";
q = string.Format("INSERT INTO xfertempl ( name, import, delimiter, fields, builtin, fieldnames, path, [sql], [table] ) "
+ "SELECT \"Units\" , False, \",\",'%s', True, True, \"c:\\\", \"SELECT units.id, units.sn, units.id1, units.id2, units.id3, clients.company, clients.first, clients.last, units.receipt, units.purchasedate, unitmodels.model, nonclients.company_person AS manufacturer, unitmodelcats.name, units.loaner, units.lastmeter "
+ "FROM ((units LEFT JOIN (unitmodels LEFT JOIN unitmodelcats ON unitmodels.category = unitmodelcats.id) ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN clients ON units.client = clients.id;\",\"sql\";", q2);
executeQuery(q);
executeQuery("ALTER TABLE wo ADD COLUMN indexed YESNO;");
executeQuery("UPDATE wo SET wo.indexed = False;");
//new account number field for non-clients
executeQuery("ALTER TABLE nonclients ADD COLUMN account TEXT(40);");
//new custref column stuff here:
executeQuery("ALTER TABLE statusviews ADD COLUMN custref INTEGER;");
executeQuery("ALTER TABLE statusviews ALTER custref SET DEFAULT '0';");
executeQuery("UPDATE statusviews SET statusviews.custref = 1;");
executeQuery("UPDATE statusviews SET statusviews.viewprofile = \"(18,18) 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17\";");
//fix of pmprofile bug where it defaults to 7 columns for new users rather
//then the actual value of 8
//set correct report control profile for PM check screen now 8 columns was 7
executeQuery("UPDATE users SET users.pmprofile = \"(8,8) 111 70 91 93 67 67 258 0 0 1 2 3 4 5 6 7\";");
executeQuery("ALTER TABLE users ALTER pmprofile SET DEFAULT \"(8,8) 111 70 91 93 67 67 258 0 0 1 2 3 4 5 6 7\";");
//Add new parts used on workorder reports to rptsmaster
//DETAILED
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Parts on workorders\", \"partwo.rpt\", \"mgr\", True, 397, 25;");
//SUMMARY
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Parts on workorder - summarized\", \"prtwosum.rpt\", \"mgr\", True, 397, 26;");
//DETAILED
q2 = "SELECT IIf(IsNull([company]),[clients].[last] & \", \" & [clients].[first],[clients].[company]) AS CLIENT, wo.id AS WORKORDER, #~STRT# AS STARTDATE, #~END_# AS ENDDATE, wo.starttime AS SCHEDULED, IIf(IsNull([partnumber]),\"misc.\",[partnumber]) AS PART, IIf(IsNull([partnumber]),[misc],[description]) AS [DESC], woparts.quantity, woparts.sn, users.last AS TECH, wo.closed, woparts.price AS WOPRICE, parts.retail, clients.id AS CLIENTID, wo.created AS WOCREATED, wo.project, wo.type "
+ "FROM (((woparts RIGHT JOIN (probs RIGHT JOIN wo ON probs.wolink = wo.id) ON woparts.link = probs.id) LEFT JOIN parts ON woparts.partnum = parts.id) LEFT JOIN users ON wo.assigntech = users.id) LEFT JOIN clients ON wo.client = clients.id "
+ "WHERE (((clients.id)~CUST) AND ((wo.created) Between #~STRT# And #~END_#) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_) AND ((woparts.id) Is Not Null)) "
+ "ORDER BY IIf(IsNull([partnumber]),\"misc.\",[partnumber]);";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=25));", q2);
executeQuery(q);
//SUMMARY
q2 = "SELECT IIf(IsNull([partnumber]),\"misc.\",[partnumber]) AS PART, IIf(IsNull([partnumber]),[misc],[description]) AS [DESC], #~STRT# AS STARTDATE, #~END_# AS ENDDATE, Sum(woparts.quantity) AS [TOTAL USED] "
+ "FROM (((woparts RIGHT JOIN (probs RIGHT JOIN wo ON probs.wolink = wo.id) ON woparts.link = probs.id) LEFT JOIN parts ON woparts.partnum = parts.id) LEFT JOIN users ON wo.assigntech = users.id) LEFT JOIN clients ON wo.client = clients.id "
+ "WHERE (((clients.id)~CUST) AND ((wo.created) Between #~STRT# And #~END_#) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_) AND ((woparts.id) Is Not Null)) "
+ "GROUP BY IIf(IsNull([partnumber]),\"misc.\",[partnumber]), IIf(IsNull([partnumber]),[misc],[description]) "
+ "ORDER BY IIf(IsNull([partnumber]),\"misc.\",[partnumber]);";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=26));", q2);
executeQuery(q);
//Add SUBREPAIR REPORTS AND QUERIES
//OUTSTANDING
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Subrepair status - outstanding\", \"subrepair.rpt\", \"mgr\", True, 401, 27;");
//ALL
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Subrepair status - all\", \"subrepairall.rpt\", \"mgr\", True, 401, 28;");
//OUTSTANDING
q2 = "SELECT IIf(IsNull([company]),[clients].[last] & \", \" & [clients].[first],[clients].[company]) AS CLIENT, wo.id AS WORKORDER, #~STRT# AS STARTDATE, #~END_# AS ENDDATE, nonclients.company_person AS SENTTO, nonclients_1.company_person AS SENTVIA, subrepair.sent AS DATESENT, subrepair.eta AS RECEIVEETA, subrepair.received AS DATERECEIVED, [nonclients_2].[company_person] & \" \" & [unitmodels].[model] & \" \" & [unitmodels].[description] & \" sn: \" & [units].[sn] AS ITEM, units.sn, nonclients_2.company_person AS MANUFACTURER, unitmodels.model, unitmodels.description, subrepair.waybill, subrepair.rma, subrepair.isback "
+ "FROM nonclients AS nonclients_2 RIGHT JOIN (((units RIGHT JOIN ((clients RIGHT JOIN (subrepair RIGHT JOIN (probs RIGHT JOIN wo ON probs.wolink = wo.id) ON subrepair.link = probs.id) ON clients.id = wo.client) LEFT JOIN nonclients ON subrepair.where = nonclients.id) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients AS nonclients_1 ON subrepair.sentvia = nonclients_1.id) ON nonclients_2.id = unitmodels.manufacturer "
+ "WHERE (((subrepair.sent) Between #~STRT# And #~END_#) AND ((subrepair.id) Is Not Null) AND ((subrepair.isback)=False) AND ((wo.client)~CUST) AND ((units.id)~UNIT)) "
+ "ORDER BY subrepair.sent;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=27));", q2);
executeQuery(q);
//ALL
q2 = "SELECT IIf(IsNull([company]),[clients].[last] & \", \" & [clients].[first],[clients].[company]) AS CLIENT, wo.id AS WORKORDER, #~STRT# AS STARTDATE, #~END_# AS ENDDATE, nonclients.company_person AS SENTTO, nonclients_1.company_person AS SENTVIA, subrepair.sent AS DATESENT, subrepair.eta AS RECEIVEETA, subrepair.received AS DATERECEIVED, [nonclients_2].[company_person] & \" \" & [unitmodels].[model] & \" \" & [unitmodels].[description] & \" sn: \" & [units].[sn] AS ITEM, units.sn, nonclients_2.company_person AS MANUFACTURER, unitmodels.model, unitmodels.description, subrepair.waybill, subrepair.rma, subrepair.isback "
+ "FROM nonclients AS nonclients_2 RIGHT JOIN (((units RIGHT JOIN ((clients RIGHT JOIN (subrepair RIGHT JOIN (probs RIGHT JOIN wo ON probs.wolink = wo.id) ON subrepair.link = probs.id) ON clients.id = wo.client) LEFT JOIN nonclients ON subrepair.where = nonclients.id) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients AS nonclients_1 ON subrepair.sentvia = nonclients_1.id) ON nonclients_2.id = unitmodels.manufacturer "
+ "WHERE (((subrepair.sent) Between #~STRT# And #~END_#) AND ((subrepair.id) Is Not Null) AND ((wo.client)~CUST) AND ((units.id)~UNIT)) "
+ "ORDER BY subrepair.sent;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=28));", q2);
executeQuery(q);
////WARNING MESSAGE ABOUT COLUMN FORMATS IN MAIN WORKORDER VIEW BEING MESSED UP
//AfxMessageBox("Part of this update modifies the main workorder list view screen to add the \r\nCUSTOMER REFERENCE NUMBER column.\r\n\r\n"
// "You will need to re-order the columns in the main view to your preferences and re-save the views.\r\n\r\n"
// "This update affects the column order only, not the underlying restriction settings.");
//fix the version number up as long as updates seem ok
executeQuery("UPDATE defaults SET defaults.versioninfo = \"144\";");
nVersion = 144;
}
break;
case 144://changed to 145 on May 23rd 2001 various updates
{
//Add securenet's fields to wo header
executeQuery("ALTER TABLE wo ADD COLUMN prob_reported TEXT(255);");
executeQuery("ALTER TABLE wo ADD COLUMN prob_found TEXT(255);");
executeQuery("ALTER TABLE wo ADD COLUMN action_taken TEXT(255);");
//Add corresponding entry to defaults to flag whether to show
//those new fields or not
executeQuery("ALTER TABLE defaults ADD COLUMN showprobaction YESNO;");
//rs->Close();
//Default to no for everyone for now
executeQuery("UPDATE defaults SET showprobaction = False;");
//fix the version number up as long as updates seem ok
executeQuery("UPDATE defaults SET defaults.versioninfo = \"145\";");
nVersion = 145;
}
break;
case 145://changed to 146 on May 27th 2001 various updates
{
//Add weeks and days fields to pm header
executeQuery("ALTER TABLE pmhead ADD COLUMN rptweeks INTEGER;");
executeQuery("ALTER TABLE pmhead ADD COLUMN rptdays INTEGER;");
executeQuery("ALTER TABLE pmhead ALTER rptweeks SET DEFAULT '0';");
executeQuery("ALTER TABLE pmhead ALTER rptdays SET DEFAULT '0';");
//rs->Close();
//set all to 0 initially
executeQuery("UPDATE pmhead SET rptweeks = 0;");
executeQuery("UPDATE pmhead SET rptdays = 0;");
//fix the version number up as long as updates seem ok
executeQuery("UPDATE defaults SET defaults.versioninfo = \"146\";");
nVersion = 146;
}
break;
case 146://changed to 147 on june 21th 2001 various updates
{
//set all blank company fields to last, first names
//2023-07-14 migrate to v8 just to ensure this works I append the record ID to ensure uniqueness to avoid the below rigamarole
executeQuery("UPDATE clients SET clients.company = [last] & \", \" & [first] & \" \" & [id] "
+ "WHERE (((clients.company) Is Null));");
//***********2023 ORIGINAL BLOCK I COMMENTED OUT
//OleDbCommand cmdr = new OleDbCommand("SELECT clients.last, clients.first, clients.bizphone FROM clients "
// + "WHERE (((clients.first) In (SELECT [first] FROM [clients] As Tmp GROUP BY [first],[last] HAVING Count(*)>1 And [last] = [clients].[last])) AND ((clients.company) Is Null)) "
// + "ORDER BY clients.last;", conn);
//OleDbDataReader rdr;
//rdr = cmdr.ExecuteReader();
////rs->QueryReadOnly("SELECT clients.last, clients.first, clients.bizphone FROM clients "
//// "WHERE (((clients.first) In (SELECT [first] FROM [clients] As Tmp GROUP BY [first],[last] HAVING Count(*)>1 And [last] = [clients].[last])) AND ((clients.company) Is Null)) "
//// "ORDER BY clients.last;");
////if(rs->IsEmpty())
//if (!rdr.Read())//meaning none
//{
// executeQuery("UPDATE clients SET clients.company = [last] & \", \" & [first] "
// + "WHERE (((clients.company) Is Null));");
// //rs->Close();
//}
//else
//{
// throw new Exception(
// "Warning: Due to a change in the clients table in the database\r\n"
// + "this update needs to move the contact first and last names to the former \r\n"
// + "\"Company\" field which is now the single client field.\r\n\r\n"
// + "Your help is required to correct a problem before the update can finish:\r\n"
// + "You have duplicate non-company clients and need to change them in the clients screen.\r\n\r\n"
// + "When this update completes and returns you to AyaNova, \r\n"
// + "go directly to the client screen (Options->Edit->Clients) where you will be given\r\n"
// + "more instructions on fixing the duplicate client names.");
// //m_pApp->ShowStuff(strData);
// //stop from doing anything further
// //buptodate=true;
// //break;
//}
//************************************************
//----------------------------------------------------------------------------
//Performance boost - new indexes
executeQuery("CREATE INDEX idxanytime ON wo (anytime);");
executeQuery("CREATE INDEX idxquick ON wo (quick);");
executeQuery("CREATE INDEX idxassigntech ON wo (assigntech);");
executeQuery("CREATE INDEX idxstarttime ON wo (starttime);");
System.Diagnostics.Debug.WriteLine("This update contains speed improvements to the database.\r\n\r\n"
+ "This will not take effect until *after* you COMPACT the database after updating.\r\n"
+ "The COMPACT option is on the database utilities menu.\r\n"
+ "(Re-indexing is not necessary, only COMPACT)");
//-----------------------------------------------------------------------------
//----------------------------------------------------------------------------
//Add new schedule groups tables
q = string.Format(
"CREATE TABLE schdgrps ("
+ "id COUNTER CONSTRAINT PK_pmitem PRIMARY KEY, "
+ "name TEXT(80));");
executeQuery(q);
q = string.Format("CREATE TABLE schdets ("
+ "grpid INTEGER DEFAULT 0, "
+ "techid INTEGER DEFAULT 0);");
executeQuery(q);
//rs->Close();
//group get's indexed because it's the searched and linked field
executeQuery("CREATE INDEX idxgrpid ON schdets (grpid);");
//techid's don't get indexed because they are simply retrieved
//-----------------------------------------------------------------------------
//create subunits table
q = string.Format("CREATE TABLE subunits ("
+ "masterunitid INTEGER DEFAULT 0, "
+ "unitid INTEGER DEFAULT 0, "
+ "creator INTEGER DEFAULT 0, "//user who created record
+ "created DATETIME, " //date created
+ "modifier INTEGER DEFAULT 0, "//date modified who cares?? probably no one
+ "modified DATETIME);");
executeQuery(q);
executeQuery("CREATE INDEX idxmstrunit "
+ "ON subunits (masterunitid);");
//-----------------------------------------------------------------------------
//add the master/sub unit report to reports screen
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Master and sub units (kits)\", \"unitkits.rpt\", \"mgr\", True, 3217, 29;");
q2 = "SELECT units.sn AS mastersn, units.id, units.client, unitmodels.manufacturer, units.model, unitmodels.description AS mastermodel, nonclients.company_person AS mastermake, unitmodelcats.name AS mastertype, units_1.sn AS subsn, unitmodels_1.description AS submodel, nonclients_1.company_person AS submake, unitmodelcats_1.name AS subtype "
+ "FROM ((((subunits RIGHT JOIN (((units LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN unitmodelcats ON unitmodels.category = unitmodelcats.id) ON subunits.masterunitid = units.id) LEFT JOIN units AS units_1 ON subunits.unitid = units_1.id) LEFT JOIN unitmodels AS unitmodels_1 ON units_1.model = unitmodels_1.id) LEFT JOIN unitmodelcats AS unitmodelcats_1 ON unitmodels_1.category = unitmodelcats_1.id) LEFT JOIN nonclients AS nonclients_1 ON unitmodels_1.manufacturer = nonclients_1.id "
+ "WHERE (((units.id)~UNIT) AND ((units.client)~CUST) AND ((unitmodels.manufacturer)~MANU) AND ((units.model)~MODL)) "
+ "ORDER BY units.sn, units.id;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=29));", q2);
executeQuery(q);
//-----------------------------------------------------------------------------
//-----------------------------------------------------------------------------
//add the rate billing summary report to reports screen
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Billing summary by rate\", \"ratesums.rpt\", \"mgr\", True, 463, 30;");
q2 = "SELECT rates.name, Sum(labor.hours) AS SumOfhours, Sum(labor.nchours) AS SumOfnchours, Sum(labor.travhours) AS SumOftravhours, rates.rate, rates.travelrate "
+ "FROM ((wo RIGHT JOIN probs ON wo.id = probs.wolink) RIGHT JOIN labor ON probs.id = labor.link) LEFT JOIN rates ON labor.rate = rates.id "
+ "WHERE (((labor.start) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((labor.tech)~TECH) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_) AND ((rates.id)~RATE) AND ((rates.active)=True)) "
+ "GROUP BY rates.name, rates.rate, rates.travelrate, rates.id UNION (SELECT rates.name, Sum(labor.hours) AS SumOfhours, Sum(labor.nchours) AS SumOfnchours, Sum(labor.travhours) AS SumOftravhours, rates.rate, rates.travelrate "
+ "FROM ((wo RIGHT JOIN probs ON wo.id = probs.wolink) RIGHT JOIN labor ON probs.id = labor.link) LEFT JOIN rates ON labor.travrate = rates.id "
+ "WHERE (((labor.start) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((labor.tech)~TECH) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_) AND ((rates.id)~RATE) AND ((rates.active)=True)) "
+ "GROUP BY rates.name, rates.rate, rates.travelrate, rates.id) "
+ "ORDER BY rates.name;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=30));", q2);
executeQuery(q);
//-----------------------------------------------------------------------------
//fix the version number up as long as updates seem ok
executeQuery("UPDATE defaults SET defaults.versioninfo = \"147\";");
nVersion = 147;
//-----------------------------------------------------------------------------
}
break;
case 147://changed to 148 on july 6th 2001 for version 1.7.1.7
{
//----------------------------------------------------------------------------
//increase size of srchdict.word from 30 characters to 50
executeQuery("ALTER TABLE srchdict ALTER COLUMN word TEXT(50);");
//increase size of srch.client from 50 characters to 80
executeQuery("ALTER TABLE srch ALTER COLUMN client TEXT(80);");
//-----------------------------------------------------------------------------
//fix the version number up as long as updates seem ok
executeQuery("UPDATE defaults SET defaults.versioninfo = \"148\";");
//-----------------------------------------------------------------------------
nVersion = 148;
}
break;
case 148:
{
//----------------------------------------------------------------------------
//Add a program version number field to the database
executeQuery("ALTER TABLE defaults ADD COLUMN min_exe TEXT(25);");
//-----------------------------------------------------------
//Add schedule markers table and indices
executeQuery("CREATE TABLE schedmarkers ("
+ "id COUNTER CONSTRAINT PK_schedmrk PRIMARY KEY, "
+ "link INTEGER DEFAULT 0,"
+ "startdate DATETIME, "
+ "enddate DATETIME, "
+ "notes TEXT(15), "
+ "red INTEGER DEFAULT 128, "
+ "green INTEGER DEFAULT 128, "
+ "blue INTEGER DEFAULT 128"
+ ");");
executeQuery("CREATE INDEX idxsmlink "
+ "ON schedmarkers (link);");
executeQuery("CREATE INDEX idxsmstdate "
+ "ON schedmarkers (startdate);");
executeQuery("CREATE INDEX idxsmendate "
+ "ON schedmarkers (enddate);");
//----------------------------------------------------------
//----------------------------------------------
executeQuery("ALTER TABLE wo ADD COLUMN starttime2 DATETIME;");
executeQuery("ALTER TABLE wo ADD COLUMN stoptime2 DATETIME;");
executeQuery("ALTER TABLE wo ADD COLUMN assigntech2 INTEGER;");
executeQuery("ALTER TABLE wo ADD COLUMN starttime3 DATETIME;");
executeQuery("ALTER TABLE wo ADD COLUMN stoptime3 DATETIME;");
executeQuery("ALTER TABLE wo ADD COLUMN assigntech3 INTEGER;");
executeQuery("ALTER TABLE wo ADD COLUMN starttime4 DATETIME;");
executeQuery("ALTER TABLE wo ADD COLUMN stoptime4 DATETIME;");
executeQuery("ALTER TABLE wo ADD COLUMN assigntech4 INTEGER;");
executeQuery("ALTER TABLE wo ALTER assigntech SET DEFAULT '-1';");
executeQuery("ALTER TABLE wo ALTER assigntech2 SET DEFAULT '-1';");
executeQuery("ALTER TABLE wo ALTER assigntech3 SET DEFAULT '-1';");
executeQuery("ALTER TABLE wo ALTER assigntech4 SET DEFAULT '-1';");
//----------------------------------------------
executeQuery("CREATE INDEX idxassigntech2 ON wo (assigntech2);");
executeQuery("CREATE INDEX idxstarttime2 ON wo (starttime2);");
executeQuery("CREATE INDEX idxassigntech3 ON wo (assigntech3);");
executeQuery("CREATE INDEX idxstarttime3 ON wo (starttime3);");
executeQuery("CREATE INDEX idxassigntech4 ON wo (assigntech4);");
executeQuery("CREATE INDEX idxstarttime4 ON wo (starttime4);");
//set extra techs to -1 meaning not assigned yet
executeQuery("UPDATE wo SET wo.assigntech2 = -1, wo.assigntech3 = -1, wo.assigntech4 = -1;");
//set non-scheduled workorders first tech to non assigned -1
executeQuery("UPDATE wo SET wo.assigntech = -1 WHERE (((wo.anytime)=True));");
//-----------------------------------------------------------------------------
//fix the version number up as long as updates seem ok
executeQuery("UPDATE defaults SET defaults.versioninfo = \"149\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.7.2.0\";");
//-----------------------------------------------------------------------------
nVersion = 149;
}
break;
case 149:
{
//Last modified 07/30/2001 - added category/type column
//COLUMNMOD
//new CATEGORY column stuff here:
executeQuery("ALTER TABLE statusviews ADD COLUMN category INTEGER;");
executeQuery("ALTER TABLE statusviews ALTER category SET DEFAULT '0';");
executeQuery("UPDATE statusviews SET statusviews.category = -1;");
//new project column here
executeQuery("ALTER TABLE statusviews ADD COLUMN project INTEGER;");
executeQuery("ALTER TABLE statusviews ALTER project SET DEFAULT '0';");
executeQuery("UPDATE statusviews SET statusviews.category = -1;");
//update all views to handle extra columns
executeQuery("UPDATE statusviews SET statusviews.viewprofile = \"(20,20) 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19\";");
//fix the version number up as long as updates seem ok
executeQuery("UPDATE defaults SET defaults.versioninfo = \"150\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.7.2.0\";");
////WARNING MESSAGE ABOUT COLUMN FORMATS IN MAIN WORKORDER VIEW BEING MESSED UP
//System.Diagnostics.Debug.WriteLine("Part of this update modifies the main workorder list view screen to add the \r\n"
// "WORKORDER CATEGORY and PROJECT columns.\r\n\r\n"
// "This change will affect *all* the views you have configured in AyaNova.\r\n"
// "You will need to re-order the columns in the main view to your preferences and re-save each view separately.\r\n\r\n"
// "This update affects the column order only, not the underlying restriction settings.\r\n"
// "\r\nDo not attempt to edit your views until after you have restarted AyaNova when this update completes.");
//-----------------------------------------------------------------------------
nVersion = 150;
}
break;
case 150:
{
//ADD AYANOTIFY TABLES
//---------------------------------------
executeQuery(
"CREATE TABLE notify_cfg (freq_mm INTEGER, [dow] INTEGER, stop_hour DATETIME, start_hour DATETIME, "
+ "[attempts] INTEGER, [mapi] YESNO, keeplogdays INTEGER, [verboseLog] YESNO, login_name TEXT(50), "
+ "login_pw TEXT(50), [connection] TEXT(120), from_address TEXT(80), [server] TEXT(120), "
+ "next_xmit DATETIME, [dialup] YESNO, last_xmit DATETIME, dayspast INTEGER , "
+ "daysfuture INTEGER);");
executeQuery("ALTER TABLE notify_cfg ALTER freq_mm SET DEFAULT '0';");
executeQuery("ALTER TABLE notify_cfg ALTER dow SET DEFAULT '0';");
executeQuery("ALTER TABLE notify_cfg ALTER attempts SET DEFAULT '0';");
executeQuery("ALTER TABLE notify_cfg ALTER keeplogdays SET DEFAULT '30';");
executeQuery("ALTER TABLE notify_cfg ALTER dayspast SET DEFAULT '0';");
executeQuery("ALTER TABLE notify_cfg ALTER daysfuture SET DEFAULT '14';");
executeQuery(
"INSERT INTO notify_cfg ( freq_mm, dow, stop_hour, start_hour, attempts, mapi, "
+ "keeplogdays, verboseLog, login_name, login_pw, [connection], from_address, server, "
+ "next_xmit, dialup, last_xmit, dayspast, daysfuture ) "
+ "SELECT 60 , 0, #12/30/1899 19:0:0#, #12/30/1899 7:0:0#, 0, False, 30, False, "
+ "\"login\", \"password\", \"My ISP\", \"from_me@my_isp.com\", \"smtp.mail.my_isp.com\", "
+ "#1/1/2000#, False, #1/1/2000#, 1, 14;");
//log table
executeQuery("CREATE TABLE notify_log ("
+ "id INTEGER DEFAULT 0, "
+ "nwhen DATETIME, "
+ "nwho INTEGER DEFAULT 0, "
+ "nwhat TEXT(255), "
+ "ntype INTEGER DEFAULT 0, "
+ "nresult TEXT(255) "
+ ");");
//temp table for building notifications
executeQuery("CREATE TABLE notify_temp ("
+ "woid INTEGER DEFAULT 0, "
+ "scheddate DATETIME, "
+ "schedstop DATETIME, "
+ "schedtech INTEGER DEFAULT 0 "
+ ");");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"151\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.7.2.0\";");
nVersion = 151;
}
break;
case 151:
{
//---------------------------------------
//add the WORKORDERS report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Workorders\", \"allwo.rpt\", \"mgr\", True, 397, 31;");
q2 = "SELECT ~CRITERIA AS CRITERIA, wo.id AS wonumber, \"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & \"Unit: \" & [nonclients].[company_person] & \" \" & [unitmodels].[description] & \" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") AS probheader, \"test\" AS compname, wom.probnum, wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, wom.PROBBRIEF, wom.item, wom.linetotal, wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created AS wodate, wo.invoice, wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, clients.bizphone, clients.extension, clients.fax, clients.email, clients.company AS clientname, wom.details, wom.START, wom.STOP,wom.REGHOURS, wom.TRAVHOURS, wom.NCHOURS, projects.name AS PROJNAME, projects.notes AS PROJNOTES, wo.client, wo.project, wo.type, wotypes.category AS CATEGORY "
+ "FROM ((((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT \"1\" AS ctype, rates.partnum AS LABPN, travelrates.partnum AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink AS wonum, \"Service: \" & users.first & \" \" & users.last & \" - \" & IIf(labor.hours>0,labor.hours & \" @ \" & Format(rates.rate,\"Currency\") & \" (pn:\" & rates.partnum & \")\",\"\") & IIf(labor.nchours>0,\" (\" & labor.nchours & \" no charge)\",\"\") & IIf(labor.travhours>0,\", \" & labor.travhours & \" travel @ \" & Format(travelrates.rate,\"Currency\") & IIf(IsNull(travelrates.partnum),\"\",\" (pn:\" & travelrates.partnum & \")\"),\"\") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) AS linetotal, labor.details , labor.start AS START, labor.stop AS STOP, labor.hours AS REGHOURS, labor.travhours AS TRAVHOURS, labor.nchours as NCHOURS FROM users INNER JOIN (rates AS travelrates INNER JOIN (rates INNER JOIN (labor INNER JOIN probs ON labor.link = probs.id) ON rates.id = labor.rate) ON travelrates.id = labor.travrate) ON users.id = labor.tech WHERE (((probs.wolink)<>0)) "
+ " LEFT JOIN parts ON woparts.partnum = parts.id WHERE (((probs.wolink)<>0)) "
+ "UNION ALL (SELECT \"3\" AS ctype,\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, \"Third party service: \" AS item, subrepair.charge AS linettotal, \"\" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM probs INNER JOIN subrepair ON probs.id = subrepair.link WHERE (((probs.wolink)<>0) AND ((subrepair.charge)<>0))))]. AS wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.id = wom.probnum) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN projects ON wo.project = projects.id ) LEFT JOIN wotypes ON wo.type = wotypes.id "
+ "WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_)) "
+ "ORDER BY wo.id;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=31));", q2);
executeQuery(q);
//------------------------------------------------
//---------------------------------------
//add the WORKORDERS BRIEF report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Workorders - brief\", \"allwobrief.rpt\", \"mgr\", True, 397, 32;");
q2 = "SELECT ~CRITERIA AS CRITERIA, wo.id AS wonumber, \"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & \"Unit: \" & [nonclients].[company_person] & \" \" & [unitmodels].[description] & \" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") AS probheader, \"test\" AS compname, wom.probnum, wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, wom.PROBBRIEF, wom.item, wom.linetotal, wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created AS wodate, wo.invoice, wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, clients.bizphone, clients.extension, clients.fax, clients.email, clients.company AS clientname, wom.details, wom.START, wom.STOP,wom.REGHOURS, wom.TRAVHOURS, wom.NCHOURS, projects.name AS PROJNAME, projects.notes AS PROJNOTES, wo.client, wo.project, wo.type, wotypes.category AS CATEGORY "
+ "FROM ((((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT \"1\" AS ctype, rates.partnum AS LABPN, travelrates.partnum AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink AS wonum, \"Service: \" & users.first & \" \" & users.last & \" - \" & IIf(labor.hours>0,labor.hours & \" @ \" & Format(rates.rate,\"Currency\") & \" (pn:\" & rates.partnum & \")\",\"\") & IIf(labor.nchours>0,\" (\" & labor.nchours & \" no charge)\",\"\") & IIf(labor.travhours>0,\", \" & labor.travhours & \" travel @ \" & Format(travelrates.rate,\"Currency\") & IIf(IsNull(travelrates.partnum),\"\",\" (pn:\" & travelrates.partnum & \")\"),\"\") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) AS linetotal, labor.details , labor.start AS START, labor.stop AS STOP, labor.hours AS REGHOURS, labor.travhours AS TRAVHOURS, labor.nchours as NCHOURS FROM users INNER JOIN (rates AS travelrates INNER JOIN (rates INNER JOIN (labor INNER JOIN probs ON labor.link = probs.id) ON rates.id = labor.rate) ON travelrates.id = labor.travrate) ON users.id = labor.tech WHERE (((probs.wolink)<>0)) "
+ "UNION ALL (SELECT \"2\" AS ctype, \"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, \"Parts: \" & IIf(woparts.misc= \"\",parts.partnumber & \" \" & parts.description & \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\") & \" ea.(sn:\" & woparts.sn & \")\",woparts.misc & \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\")& \" ea.(sn:\" & woparts.sn & \")\") AS item, woparts.quantity*woparts.price AS linetotal, \"\" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM (probs INNER JOIN woparts ON probs.id = woparts.link) "
+ "LEFT JOIN parts ON woparts.partnum = parts.id WHERE (((probs.wolink)<>0)) "
+ "UNION ALL (SELECT \"3\" AS ctype,\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, \"Third party service: \" AS item, subrepair.charge AS linettotal, \"\" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM probs INNER JOIN subrepair ON probs.id = subrepair.link WHERE (((probs.wolink)<>0) AND ((subrepair.charge)<>0))))]. AS wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.id = wom.probnum) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN projects ON wo.project = projects.id ) LEFT JOIN wotypes ON wo.type = wotypes.id "
+ "WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_)) "
+ "ORDER BY wo.id;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=32));", q2);
executeQuery(q);
//------------------------------------------------
//---------------------------------------
//add the Units by P.M. report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Units by P.M.\", \"pmunits.rpt\", \"mgr\", True, 2193, 33;");
q2 = "SELECT ~CRITERIA AS CRITERIA, pmhead.id, nonclients.company_person AS MAKE, unitmodels.model, units.sn, unitmodels.description AS UNITDESC, units.id1, units.id2, units.id3, clients.company, nonclients.phone, pmhead.description, pmhead.notes, pmhead.nextsrvdate, pmhead.nextsrvmeter, units.lastmeter, pmhead.woconvert, pmhead.repeatevery AS rptmonths_count, pmhead.rptweeks, pmhead.rptdays, units.purchasedate, pmhead.ageyears, pmhead.agemonths, users.initials, users.first, users.last, pmhead.isclient "
+ "FROM ((pmhead LEFT JOIN ((units LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) ON pmhead.link = units.id) LEFT JOIN clients ON units.client = clients.id) LEFT JOIN users ON pmhead.tech = users.id "
+ "WHERE (((pmhead.isclient)=False) AND ((units.client)~CUST) AND ((units.id)~UNIT) AND ((units.model)~MODL)) "
+ "ORDER BY nonclients.company_person, unitmodels.model, units.sn, clients.company;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=33));", q2);
executeQuery(q);
//------------------------------------------------
//---------------------------------------
//add the P.M. by Units report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"P.M. by Units\", \"unitspm.rpt\", \"mgr\", True, 2193, 34;");
q2 = "SELECT ~CRITERIA AS CRITERIA, pmhead.id, nonclients.company_person AS MAKE, unitmodels.model, units.sn, unitmodels.description AS UNITDESC, units.id1, units.id2, units.id3, clients.company, nonclients.phone, pmhead.description, pmhead.notes, pmhead.nextsrvdate, pmhead.nextsrvmeter, units.lastmeter, pmhead.woconvert, pmhead.repeatevery AS rptmonths_count, pmhead.rptweeks, pmhead.rptdays, units.purchasedate, pmhead.ageyears, pmhead.agemonths, users.initials, users.first, users.last, pmhead.isclient "
+ "FROM ((pmhead LEFT JOIN ((units LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) ON pmhead.link = units.id) LEFT JOIN clients ON units.client = clients.id) LEFT JOIN users ON pmhead.tech = users.id "
+ "WHERE (((pmhead.isclient)=False) AND ((units.client)~CUST) AND ((units.id)~UNIT) AND ((units.model)~MODL)) "
+ "ORDER BY nonclients.company_person, unitmodels.model, units.sn, clients.company;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=34));", q2);
executeQuery(q);
//------------------------------------------------
//---------------------------------------
//add the P.M. by Client report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"P.M. by Client\", \"clientpm.rpt\", \"mgr\", True, 385, 35;");
q2 = "SELECT ~CRITERIA AS CRITERIA, clients.company, pmhead.description, pmhead.notes, pmhead.nextsrvdate, pmhead.link AS CLIENTID, pmhead.woconvert, pmhead.repeatevery AS rptmonths_count, pmhead.rptweeks, pmhead.rptdays, pmhead.ageyears, pmhead.agemonths, users.initials, users.first AS TECH_FIRST, users.last AS TECH_LAST "
+ "FROM clients RIGHT JOIN (pmhead LEFT JOIN users ON pmhead.tech = users.id) ON clients.id = pmhead.link "
+ "WHERE (((pmhead.nextsrvdate) Between #~STRT# And #~END_#) AND ((pmhead.link)~CUST) AND ((pmhead.isclient)=True)) "
+ "ORDER BY clients.company;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=35));", q2);
executeQuery(q);
//------------------------------------------------
//---------------------------------------
//add the Workorders not invoiced report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Workorders not invoiced\", \"notbilled.rpt\", \"mgr\", True, 385, 36;");
q2 = "SELECT ~CRITERIA AS CRITERIA, wo.id, clients.company, wo.modified, wo.ourref, wo.clientrefnum, wo.clientcontact, wo.closed, wo.invoice, wo.notes "
+ "FROM probstat RIGHT JOIN (wo LEFT JOIN clients ON wo.client = clients.id) ON probstat.id = wo.status "
+ "WHERE (((wo.modified) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((wo.invoice) Is Null)) "
+ "ORDER BY wo.modified DESC;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=36));", q2);
executeQuery(q);
//------------------------------------------------
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"152\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.7.2.0\";");
nVersion = 152;
}
break;
case 152: //update for v1720 added extra security rights items
{
//2023-07-14 migrate to v8 - I'm going to ignore this for now hopefully not necessary
//CString strID,strName,strRights,strHash;
//long lData;
//GZK k;
////Add new right:
////loop through all the group records
//rs->Query("SELECT groups.* FROM groups;");
//do{
// rs->FetchField("id",&lData);
// rs->FetchField("a",&strName);
// rs->FetchField("b",&strRights);
// //decrypt the original rights string
// k.GZDecrypt(&strRights,false);
// strRights=strRights+"111";//add on the extra right for schedule view
// //re-'crypt'
// k.GZEncrypt(&strRights,false);
// //HASH
// strHash.Format("%u%s%s",lData,strName,strRights);
// k.GZHash(&strHash);
// //save the rights
// rs->UpdateField("b",&strRights);
// //save the hash
// rs->UpdateField("c",&strHash);
// //save the record
// if(!rs->SaveRecord())
// AfxMessageBox("Error trying to save rights record");
//}while(rs->MoveForward());
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"153\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.7.2.0\";");
nVersion = 153;
}
break;
case 153: //update for v1720 modified client equipment report query
//and added eval field for web server
{
//"KJB3BoICxnQM9KvUO+1mzQ==" Evaluate 8 chars
executeQuery("ALTER TABLE defaults ADD COLUMN mango TEXT(80);");
executeQuery("UPDATE defaults SET defaults.mango = \"KJB3BoICxnQM9KvUO+1mzQ\";");
/*
How web server mango column works:
if = Evaluate then means ready to be evaluated
first time webserver runs sets mango to expiry date
when expires sets column to Expired and web server stops working
*/
//ADD EMAIL NOTIFICATION FIELDS TO USERS TABLE (oops forgot to do it earlier)
executeQuery("ALTER TABLE users ADD COLUMN email TEXT(120);");
executeQuery("ALTER TABLE users ADD COLUMN emailnotify YESNO;");
executeQuery("ALTER TABLE users ALTER emailnotify SET DEFAULT '0';");
executeQuery("UPDATE users SET users.emailnotify = False;");
//UPDATE CLIENT EQUIPMENT REPORT QUERY
q2 = "SELECT clients.id, IIf(IsNull([clients].[company]),[clients].[last] & \", \" & [clients].[first],[clients].[company]) AS client, [clients].[first] & \" \" & [clients].[last] AS clientcontact, clients.bizphone, [manufacturers].[company_person] & \" \" & [unitmodels].[model] AS item, units.sn, IIf([units].[boughthere],\"us\",[nonclients].[company_person]) AS vendor, units.purchasedate, units.receipt AS invoice, units.notes, units.description "
+ "FROM (((units LEFT JOIN clients ON units.client = clients.id) LEFT JOIN nonclients ON units.purchasedfrom = nonclients.id) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients AS manufacturers ON unitmodels.manufacturer = manufacturers.id "
+ "WHERE (((clients.id)~CUST) AND ((units.purchasedate) Between #~STRT# And #~END_#) AND ((units.loaner)=False)) "
+ "ORDER BY IIf(IsNull([clients].[company]),[clients].[last] & \", \" & [clients].[first],[clients].[company]);";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=20));", q2);
executeQuery(q);
//UPDATE UNIT HISTORY REPORT QUERY
q2 = "SELECT ~USERNAME AS zCurrentUser, probs.id, probs.wolink AS WORKORDERID, probs.brief AS PROBBRIEF, probs.notes "
+ "AS PROBNOTES, Format([start],\"Short Date\") AS servdate, labor.details, clients.company AS clientcompany, "
+ "units.sn, unitmodels.model, users.initials, probs.meter, ztotalparts.totalparts, ztotallabor.totallabor FROM "
+ "[SELECT Sum((labor.hours*rates.rate)+(labor.travhours*travrates.rate)) AS totallabor, probs.id FROM probs INNER "
+ "JOIN (rates INNER JOIN (rates AS travrates INNER JOIN labor ON travrates.id = labor.rate) ON rates.id = labor.rate) "
+ "ON probs.id = labor.link GROUP BY probs.id]. AS ztotallabor RIGHT JOIN ([SELECT IIf(IsNull(Sum(woparts.quantity*woparts.price)), 0,Sum(woparts.quantity*woparts.price)) AS totalparts, "
+ "probs.id FROM probs LEFT JOIN woparts ON probs.id = woparts.link GROUP BY probs.id]. AS ztotalparts RIGHT "
+ "JOIN (users RIGHT JOIN ((labor RIGHT JOIN (((wo RIGHT JOIN probs ON wo.id = probs.wolink) LEFT JOIN units "
+ "ON probs.unit = units.id) LEFT JOIN unitmodels ON units.model = unitmodels.id) ON labor.link = probs.id) LEFT "
+ "JOIN clients ON wo.client = clients.id) ON users.id = labor.tech) ON ztotalparts.id = probs.id) ON "
+ "ztotallabor.id = probs.id WHERE (((probs.unit)~UNIT) AND ((units.sn) Is Not Null) AND ((labor.start) "
+ "Between #~STRT# And #~END_#)) ORDER BY labor.start DESC;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=7));", q2);
executeQuery(q);
//UPDATE CALLBACKS BY DATE
q = "UPDATE rptsmaster SET rptsmaster.x = True "
+ "WHERE (((rptsmaster.id)=2));";
executeQuery(q);
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"154\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.7.2.0\";");
nVersion = 154;
}
break;
case 154: //update for v1721
{
executeQuery("ALTER TABLE users ADD COLUMN schedrnd YESNO;");
executeQuery("UPDATE users SET users.schedrnd = True;");
executeQuery("ALTER TABLE users ADD COLUMN schedoneday YESNO;");
executeQuery("UPDATE users SET users.schedoneday = False;");
executeQuery("ALTER TABLE rentals ADD COLUMN ref TEXT(80);");
executeQuery("ALTER TABLE users ALTER COLUMN rentalprofile TEXT(80);");
executeQuery("ALTER TABLE users ALTER rentalprofile SET DEFAULT \"(9,9) 42 169 195 103 88 129 0 105 109 0 1 8 7 2 3 4 5 6\";");
executeQuery("UPDATE users SET users.rentalprofile = '(9,9) 42 169 195 103 88 129 0 105 109 0 1 8 7 2 3 4 5 6';");
//---------------------------------------
//add the LOANERS report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"loaners\", \"loaners.rpt\", \"mgr\", False, 32767, 37;");
//------------------------------------------------
//---------------------------------------
//add the RESPONSE TIME BY PROBLEM report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Response time by problem item (first response)\", \"respprob.rpt\", \"mgr\", True, 399, 38;");
q2 = "SELECT ~CRITERIA AS CRITERIA, probs.created AS prob_entered, probs.wolink, Min(labor.start) AS First_response, wo.id AS Workorder, wotypes.category AS CategoryName, projects.name AS ProjectName, clients.company AS ClientName, probs.brief "
+ "FROM (((labor LEFT JOIN (wo RIGHT JOIN probs ON wo.id = probs.wolink) ON labor.link = probs.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN projects ON wo.project = projects.id) LEFT JOIN wotypes ON wo.type = wotypes.id "
+ "WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((wo.project)~PROJ) AND ((wo.client)~CUST) AND ((wo.type)~CAT_) AND ((labor.tech)~TECH) AND ((wo.quick)=False)) "
+ "GROUP BY probs.created, probs.wolink, wo.id, wotypes.category, projects.name, clients.company, probs.brief, wo.created "
+ "HAVING (((Min(labor.start)) Is Not Null)) "
+ "ORDER BY wo.created DESC , probs.wolink;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=38));", q2);
executeQuery(q);
//------------------------------------------------
//---------------------------------------
//add the RESPONSE TIME BY WORKORDER report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Response time for standard WO (first response)\", \"respwo.rpt\", \"mgr\", True, 399, 39;");
q2 = "SELECT ~CRITERIA AS CRITERIA, wo.created AS wo_entered, probs.wolink, Min(labor.start) AS First_response, wo.id AS Workorder, wotypes.category AS CategoryName, projects.name AS ProjectName, clients.company AS ClientName "
+ "FROM (((labor LEFT JOIN (wo RIGHT JOIN probs ON wo.id = probs.wolink) ON labor.link = probs.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN projects ON wo.project = projects.id) LEFT JOIN wotypes ON wo.type = wotypes.id "
+ "WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((wo.project)~PROJ) AND ((wo.client)~CUST) AND ((wo.type)~CAT_) AND ((labor.tech)~TECH) AND ((wo.quick)=False)) "
+ "GROUP BY wo.created, probs.wolink, wo.id, wotypes.category, projects.name, clients.company "
+ "HAVING (((Min(labor.start)) Is Not Null)) "
+ "ORDER BY wo.created DESC , probs.wolink;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=39));", q2);
executeQuery(q);
//------------------------------------------------
//ADD the tables for ARAS clients
//------------------------------------------------
//clients_aras
executeQuery("CREATE TABLE clients_aras ("
+ "id COUNTER CONSTRAINT PK_clntaras PRIMARY KEY, "
+ "clientlink INTEGER DEFAULT 0,"
+ "loginid TEXT(50), "
+ "loginpw TEXT(50), "
+ "view_wo_status YESNO DEFAULT False, "
+ "search_history YESNO DEFAULT False, "
+ "wo_view YESNO DEFAULT False, "
+ "wo_report YESNO DEFAULT False, "
+ "request_service YESNO DEFAULT False, "
+ "view_request_list YESNO DEFAULT False, "
+ "private_notes MEMO, "
+ "last_login_date DATETIME, "
+ "creator INTEGER DEFAULT 0, "//user who created record
+ "created DATETIME, " //date created
+ "modifier INTEGER DEFAULT 0, "//date modified
+ "modified DATETIME "
+ ");");
//------------------------------------------------
//client_requests
executeQuery("CREATE TABLE client_requests ("
+ "id COUNTER CONSTRAINT PK_clntrqsts PRIMARY KEY, "
+ "clientlink INTEGER DEFAULT 0,"
+ "requestor_id INTEGER DEFAULT 0,"
+ "workorderid INTEGER DEFAULT 0,"
+ "request TEXT(110), "
+ "probdetails MEMO, "
+ "prefertech INTEGER DEFAULT -1,"
+ "refnum TEXT(80), "
+ "ournotes MEMO, "
+ "created DATETIME, " //date created
+ "urgency INTEGER DEFAULT 0"//0=not 2=extreme
+ ");");
//------------------------------------------------
//ADD new profile key to user table for ARAS request view screen
executeQuery("ALTER TABLE users ADD COLUMN aras_req_profile TEXT(80);");
executeQuery("ALTER TABLE users ALTER aras_req_profile SET DEFAULT \"(7,7) 52 76 128 73 217 162 0 0 1 2 3 4 5 6\";");
executeQuery("UPDATE users SET users.aras_req_profile = '(7,7) 52 76 128 73 217 162 0 0 1 2 3 4 5 6';");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"155\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.7.2.1\";");
nVersion = 155;
}
break;
case 155://changed to 156 on Sept 30th 2002 for version 1.9
{
//Added field to turn on or off transaction support
//true=database execute queries are wrapped in transactions
//false=old style pre 1.9 not wrapped in transactions
//made it an option in case users have problems
//should improve data currency on a busy network
executeQuery("ALTER TABLE defaults ADD COLUMN transact YESNO;");
executeQuery("UPDATE defaults SET defaults.transact = True;");
//client alternate phone number
executeQuery("ALTER TABLE clients ADD COLUMN phone2 TEXT(40);");
executeQuery("ALTER TABLE clients ADD COLUMN phone3 TEXT(40);");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"156\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 156;
}
break;
case 156://changed to 157 on Oct 4th 2002 for version 1.9
{
//Horizontal grid on main work order list screen
//user preference field. Default to true so they
//know it's there and we can get all those emails
//bitching about it because they don't read the docs and know that
//they can turn it off <grin>
executeQuery("ALTER TABLE users ADD COLUMN hgrid YESNO;");
executeQuery("UPDATE users SET users.hgrid = True;");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"157\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 157;
break;
}
case 157://changed to 158 on Oct 8th 2002 for version 1.9
{
//drop location field in wo which is not used and
//add a pmid field
executeQuery("ALTER TABLE wo ADD COLUMN pmid INTEGER;");
executeQuery("ALTER TABLE wo ALTER pmid SET DEFAULT '0';");
executeQuery("UPDATE wo SET wo.pmid = 0;");
executeQuery("ALTER TABLE wo DROP COLUMN location;");
//update pmhead table to include the two new time fields
executeQuery("ALTER TABLE pmhead ADD COLUMN starttime DATETIME;");
executeQuery("ALTER TABLE pmhead ADD COLUMN endtime DATETIME;");
executeQuery("UPDATE pmhead SET pmhead.starttime = #10/8/2002 8:0:0#;");
executeQuery("UPDATE pmhead SET pmhead.endtime = #10/8/2002 9:0:0#;");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"158\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 158;
break;
}
case 158://changed to 159 on Oct 9th 2002 for version 1.9
{
//-2 now means don't show scheduled tech column in workorder list
//where it used to be 0 for that so change all 0's to -2's
executeQuery("UPDATE statusviews SET statusviews.schedtech = -2 "
+ "WHERE (((statusviews.schedtech)=0));");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"159\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 159;
break;
}
case 159://changed to 160 on Oct 11th 2002 for version 1.9
{
//add created days column to status views table
executeQuery("ALTER TABLE statusviews ADD COLUMN createddays INTEGER;");
executeQuery("ALTER TABLE statusviews ALTER createddays SET DEFAULT '0';");
executeQuery("UPDATE statusviews SET statusviews.createddays = 0;");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"160\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 160;
break;
}
case 160://changed to 161 on Oct 12th 2002 for version 1.9
{
//Add distance column to labor table
executeQuery("ALTER TABLE labor ADD COLUMN distance REAL;");
executeQuery("ALTER TABLE labor ALTER distance SET DEFAULT '0';");
executeQuery("UPDATE labor SET labor.distance = 0;");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"161\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 161;
break;
}
case 161://changed to 162 on Oct 12th 2002 for version 1.9
{
//Add distance column to labor table
executeQuery("ALTER TABLE clients ADD COLUMN inactive YESNO;");
executeQuery("ALTER TABLE clients ALTER inactive SET DEFAULT '0';");
executeQuery("UPDATE clients SET clients.inactive = False;");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"162\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 162;
break;
}
case 162://changed to 163 on Oct 12th 2002 for version 1.9
{
//Set all woparts.misc to NULL as some may be empty
//other changes will ensure it is never set to empty in future
//this is just to catch any existing ones.
executeQuery("UPDATE woparts SET woparts.misc = Null WHERE (((woparts.misc)=\"\"));");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"163\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 163;
break;
}
case 163://changed to 164 on Oct 13th 2002 for version 1.9
{
//Add subquery field to rptsmaster
//required for parts printing on dispatch standard workorder
executeQuery("ALTER TABLE rptsmaster ADD COLUMN subreportquery MEMO;");
//REMOVE ANY OLD RPTS MASTER ENTRIES FOR WORKORDER REPORTS
//STANDARD DISPATCH
q2 = "SELECT \"~ESTRATE\" AS estrate, \"~SCHEDTECH\" AS scheduledtech, "
+ "~ESTHOURS AS esthours, IIf(IsNull([clients].[company]),[clients].[last] "
+ "& \", \" & [clients].[first],[clients].[company]) "
+ "AS clientname, IIf(IsNull([clients].[streetaddress]),[clients].[mailaddress],[clients].[streetaddress]) "
+ "& IIf(IsNull([clients].[city]),\"\",Chr(13) "
+ "& [clients].[city]) & IIf(IsNull([clients].[stateprov]),\"\",\", "
+ "\" & [clients].[stateprov] "
+ "& IIf(IsNull([clients].[postal]),\"\",\" "
+ "\" & [clients].[postal])) AS address, \"Work required: "
+ "\" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) "
+ "& \"Unit: \" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] & \" (\" & [unitmodels].[model] "
+ "& \") SN: \" & [units].[sn],\"\") AS probheader, "
+ "\"~REGTO\" AS compname, wo.notes AS wonotes, "
+ "wo.clientrefnum, wo.clientcontact, wo.ourref, "
+ "wo.prob_reported, wo.prob_found, wo.action_taken, "
+ "wo.id AS wonumber, wo.created, labor.details, probs.notes, "
+ "IIf(IsNull(contracts_1.name),[contracts].[name],contracts_1.name) "
+ "AS contractinfo, IIf([probs].[unit]<>0,\"Unit: "
+ "\" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] & \" (\" "
+ "& [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") "
+ "AS Equipment, wo.starttime AS [Booked for], wo.stoptime "
+ "AS [Booked for END], wo.closed, probs.brief, "
+ "clients.mailaddress, clients.streetaddress, clients.city, "
+ "clients.stateprov, clients.postal, clients.country, "
+ "clients.bizphone, clients.extension, "
+ "clients.technotes, clients.first, clients.last, probstat.notes "
+ "AS STATUS, probstat_1.notes AS ITEMSTATUS, "
+ "projects.name AS PROJNAME, projects.notes AS "
+ "PROJNOTES FROM projects RIGHT JOIN ((((((labor RIGHT "
+ "JOIN (((units RIGHT JOIN probs ON units.id = "
+ "probs.unit) LEFT JOIN unitmodels ON units.model = "
+ "unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer "
+ "= nonclients.id) ON labor.link = probs.id) "
+ "RIGHT JOIN ((wo LEFT JOIN clients ON wo.client "
+ "= clients.id) LEFT JOIN clients AS headoffices "
+ "ON clients.headoffice = headoffices.id) ON probs.wolink "
+ "= wo.id) LEFT JOIN contracts ON clients.contract "
+ "= contracts.id) LEFT JOIN contracts AS contracts_1 "
+ "ON headoffices.contract = contracts_1.id) LEFT "
+ "JOIN probstat ON wo.status = probstat.id) LEFT "
+ "JOIN probstat AS probstat_1 ON probs.status = probstat_1.id) "
+ "ON projects.id = wo.project WHERE (((wo.id)=~WOID)); ";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=21));", q2);
executeQuery(q);
//***** SUBREPORT ******************
q2 = "SELECT probs.id, woparts.quantity, woparts.sn, parts.partnumber, "
+ "woparts.misc, parts.description, parts.retail, "
+ "IIf(IsNull(woparts.misc),[parts].[partnumber] "
+ "& \" \" & [parts].[description] & \": \" & [woparts].[quantity] "
+ "& \" @ \" & Format([woparts].[price],\"Currency\") "
+ "& \" ea.\" & IIf(IsNull([woparts].[sn]),\"\",\" sn:\" & [woparts].[sn]) "
+ ", [woparts].[misc] "
+ "& \": \" & [woparts].[quantity] & \" @ \" & Format([woparts].[price],\"Currency\") "
+ "& \" ea.sn:\" & [woparts].[sn] "
+ ") AS item, [woparts].[quantity]*[woparts].[price] "
+ "AS linetotal FROM (woparts RIGHT JOIN "
+ "(probs RIGHT JOIN wo ON probs.wolink = wo.id) ON "
+ "woparts.link = probs.id) LEFT JOIN parts ON woparts.partnum "
+ "= parts.id WHERE (((woparts.quantity) "
+ "Is Not Null) AND ((wo.id)=~WOID));";
q = string.Format("UPDATE rptsmaster SET rptsmaster.subreportquery = '%s' "
+ "WHERE (((rptsmaster.id)=21));", q2);
executeQuery(q);
//------------------------------------------------
//STANDARD BRIEF
q2 = "SELECT \"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) "
+ "& \"Unit: \" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] "
+ "& \" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") "
+ "AS probheader, \"~REGTO\" AS compname, wom.probnum, "
+ "wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, "
+ "wom.PROBBRIEF, wom.item, wom.linetotal, "
+ "wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created "
+ "AS wodate, wo.id AS wonumber, wo.invoice, "
+ "wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, "
+ "clients.billheadoffice, clients.first, clients.last, "
+ "clients.mailaddress, clients.streetaddress, "
+ "clients.city, clients.stateprov, clients.postal, "
+ "clients.country, clients.bizphone, clients.extension, "
+ "clients.fax, clients.email, [clients].[company] "
+ "AS clientname, IIf(IsNull([headoffices].[company]),[headoffices].[last] "
+ "& \", \" & [headoffices].[first],[headoffices].[company]) "
+ "AS headname, wom.details, "
+ "wom.STARTDATE, wom.STOPDATE, projects.name "
+ "AS PROJNAME, projects.notes AS PROJNOTES FROM "
+ "(((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT "
+ "\"1\" AS ctype, rates.partnum AS LABPN, travelrates.partnum "
+ "AS TRAVPN, probs.notes AS PROBNOTES, probs.brief "
+ "AS PROBBRIEF, probs.id AS probnum, probs.wolink "
+ "AS wonum, \"Service: \" & users.first & \" \" & "
+ "users.last & \" - \" & IIf(labor.hours>0,labor.hours "
+ "& \" hrs. @ \" & Format(rates.rate,\"Currency\") & \" "
+ "(pn:\" & rates.partnum & \")\",\"\") & IIf(labor.nchours>0,\" "
+ "(\" & labor.nchours & \" hrs. no charge)\",\"\") "
+ "& IIf(labor.travhours>0,\", \" & labor.travhours & \" "
+ "hrs. travel @ \" & Format(travelrates.rate,\"Currency\") "
+ "& IIf(IsNull(travelrates.partnum),\"\",\" (pn:\" "
+ "& travelrates.partnum & \")\"),\"\") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) "
+ "AS linetotal,labor.details, labor.start AS STARTDATE, "
+ "labor.stop as STOPDATE FROM users INNER JOIN "
+ "(rates AS travelrates INNER JOIN (rates INNER JOIN "
+ "(labor INNER JOIN probs ON labor.link = probs.id) "
+ "ON rates.id = labor.rate) ON travelrates.id = labor.travrate) "
+ "ON users.id = labor.tech WHERE (((probs.wolink)=~WOID)) "
+ "UNION ALL (SELECT \"2\" AS ctype, "
+ "\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, "
+ "probs.brief AS PROBBRIEF, probs.id AS probnum, "
+ " probs.wolink as wonum, \"Parts: \" & IIf(IsNull(woparts.misc),parts.partnumber "
+ "& \" \" & parts.description "
+ "& \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\") "
+ "& \" ea.(sn:\" & woparts.sn "
+ "& \")\",woparts.misc & \": \" & woparts.quantity "
+ "& \" @ \" & Format(woparts.price,\"Currency\")& \" ea.(sn:\" "
+ "& woparts.sn & \")\") AS item, woparts.quantity*woparts.price "
+ "AS linetotal,\"\" AS details, #03/12/1968# "
+ "AS STARTDATE, #03/12/1968# AS STOPDATE FROM "
+ "(probs INNER JOIN woparts ON probs.id = woparts.link) "
+ "LEFT JOIN parts ON woparts.partnum = parts.id "
+ "WHERE (((probs.wolink)=~WOID)) UNION ALL (SELECT "
+ " \"3\" AS ctype,\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes "
+ "AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id "
+ "AS probnum, probs.wolink as wonum, \"Third party "
+ "service: \" AS item, subrepair.charge AS linettotal, "
+ "\"\" AS details, #03/12/1968# AS STARTDATE, #03/12/1968# "
+ "AS STOPDATE FROM probs INNER JOIN subrepair "
+ "ON probs.id = subrepair.link WHERE (((probs.wolink)=~WOID) "
+ "AND ((subrepair.charge)<>0))))]. AS "
+ "wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN "
+ "clients ON wo.client = clients.id) LEFT JOIN clients "
+ "AS headoffices ON clients.headoffice = headoffices.id) "
+ "ON probs.id = wom.probnum) ON units.id = probs.unit) "
+ "LEFT JOIN unitmodels ON units.model = unitmodels.id) "
+ "LEFT JOIN nonclients ON unitmodels.manufacturer "
+ "= nonclients.id) LEFT JOIN projects ON "
+ "wo.project = projects.id ORDER BY wom.STARTDATE DESC; ";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=1));", q2);
executeQuery(q);
//------------------------------------------------
//STANDARD COMPLETED
q2 = "SELECT \"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) "
+ "& \"Unit: \" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] "
+ "& \" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") "
+ "AS probheader, \"~REGTO\" AS compname, wom.probnum, "
+ "wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, "
+ "wom.PROBBRIEF, wom.item, wom.linetotal, "
+ "wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created "
+ "AS wodate, wo.id AS wonumber, wo.invoice, "
+ "wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, "
+ "clients.billheadoffice, clients.first, clients.last, "
+ "clients.mailaddress, clients.streetaddress, "
+ "clients.city, clients.stateprov, clients.postal, "
+ "clients.country, clients.bizphone, clients.extension, "
+ "clients.fax, clients.email, [clients].[company] "
+ "AS clientname, IIf(IsNull([headoffices].[company]),[headoffices].[last] "
+ "& \", \" & [headoffices].[first],[headoffices].[company]) "
+ "AS headname, wom.details, "
+ "wom.STARTDATE, wom.STOPDATE, projects.name "
+ "AS PROJNAME, projects.notes AS PROJNOTES FROM "
+ "(((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT "
+ "\"1\" AS ctype, rates.partnum AS LABPN, travelrates.partnum "
+ "AS TRAVPN, probs.notes AS PROBNOTES, probs.brief "
+ "AS PROBBRIEF, probs.id AS probnum, probs.wolink "
+ "AS wonum, \"Service: \" & users.first & \" \" & "
+ "users.last & \" - \" & IIf(labor.hours>0,labor.hours "
+ "& \" hrs. @ \" & Format(rates.rate,\"Currency\") & \" "
+ "(pn:\" & rates.partnum & \")\",\"\") & IIf(labor.nchours>0,\" "
+ "(\" & labor.nchours & \" hrs. no charge)\",\"\") "
+ "& IIf(labor.travhours>0,\", \" & labor.travhours & \" "
+ "hrs. travel @ \" & Format(travelrates.rate,\"Currency\") "
+ "& IIf(IsNull(travelrates.partnum),\"\",\" (pn:\" "
+ "& travelrates.partnum & \")\"),\"\") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) "
+ "AS linetotal,labor.details, labor.start AS STARTDATE, "
+ "labor.stop as STOPDATE FROM users INNER JOIN "
+ "(rates AS travelrates INNER JOIN (rates INNER JOIN "
+ "(labor INNER JOIN probs ON labor.link = probs.id) "
+ "ON rates.id = labor.rate) ON travelrates.id = labor.travrate) "
+ "ON users.id = labor.tech WHERE (((probs.wolink)=~WOID)) "
+ "UNION ALL (SELECT \"2\" AS ctype, "
+ "\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, "
+ "probs.brief AS PROBBRIEF, probs.id AS probnum, "
+ " probs.wolink as wonum, \"Parts: \" & IIf(IsNull(woparts.misc),parts.partnumber "
+ "& \" \" & parts.description "
+ "& \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\") "
+ "& \" ea.(sn:\" & woparts.sn "
+ "& \")\",woparts.misc & \": \" & woparts.quantity "
+ "& \" @ \" & Format(woparts.price,\"Currency\")& \" ea.(sn:\" "
+ "& woparts.sn & \")\") AS item, woparts.quantity*woparts.price "
+ "AS linetotal,\"\" AS details, #03/12/1968# "
+ "AS STARTDATE, #03/12/1968# AS STOPDATE FROM "
+ "(probs INNER JOIN woparts ON probs.id = woparts.link) "
+ "LEFT JOIN parts ON woparts.partnum = parts.id "
+ "WHERE (((probs.wolink)=~WOID)) UNION ALL (SELECT "
+ " \"3\" AS ctype,\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes "
+ "AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id "
+ "AS probnum, probs.wolink as wonum, \"Third party "
+ "service: \" AS item, subrepair.charge AS linettotal, "
+ "\"\" AS details, #03/12/1968# AS STARTDATE, #03/12/1968# "
+ "AS STOPDATE FROM probs INNER JOIN subrepair "
+ "ON probs.id = subrepair.link WHERE (((probs.wolink)=~WOID) "
+ "AND ((subrepair.charge)<>0))))]. AS "
+ "wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN "
+ "clients ON wo.client = clients.id) LEFT JOIN clients "
+ "AS headoffices ON clients.headoffice = headoffices.id) "
+ "ON probs.id = wom.probnum) ON units.id = probs.unit) "
+ "LEFT JOIN unitmodels ON units.model = unitmodels.id) "
+ "LEFT JOIN nonclients ON unitmodels.manufacturer "
+ "= nonclients.id) LEFT JOIN projects ON "
+ "wo.project = projects.id ORDER BY wom.STARTDATE DESC; ";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=19));", q2);
executeQuery(q);
//------------------------------------------------
//QUICK DISPATCH WORKORDER
q2 = "SELECT IIf(IsNull([clients].[company]),[clients].[last] "
+ "& \", \" & [clients].[first],[clients].[company]) "
+ "AS clientname, IIf(IsNull([clients].[streetaddress]),[clients].[mailaddress],[clients].[streetaddress]) "
+ "& IIf(IsNull([clients].[city]),\"\",[clients].[city]) "
+ "& IIf(IsNull([clients].[stateprov]),\"\",\", "
+ "\" & [clients].[stateprov] & IIf(IsNull([clients].[postal]),\"\",\" "
+ "\" & [clients].[postal])) AS address, "
+ "\"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) "
+ "& \"Unit: \" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] & "
+ "\" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") "
+ "AS probheader, \"~REGTO\" AS compname, wo.id "
+ "AS wonumber, wo.created, labor.details, probs.notes, "
+ "IIf(IsNull(contracts_1.name),[contracts].[name],contracts_1.name) "
+ "AS contractinfo, IIf([probs].[unit]<>0,\"Unit: "
+ "\" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] & \" (\" & [unitmodels].[model] "
+ "& \") SN: \" & [units].[sn],\"\") AS "
+ "Equipment, wo.starttime AS [Booked for], probs.brief, "
+ "clients.mailaddress, clients.streetaddress, "
+ "clients.city, clients.stateprov, clients.postal, clients.country, "
+ "clients.bizphone, clients.extension, "
+ "clients.technotes, clients.first, clients.last, "
+ "wo.clientrefnum, wo.ourref, wo.clientcontact FROM "
+ "(((labor RIGHT JOIN (((units RIGHT JOIN probs ON "
+ "units.id = probs.unit) LEFT JOIN unitmodels ON units.model "
+ "= unitmodels.id) LEFT JOIN nonclients ON "
+ "unitmodels.manufacturer = nonclients.id) ON labor.link "
+ "= probs.id) RIGHT JOIN ((wo LEFT JOIN clients "
+ "ON wo.client = clients.id) LEFT JOIN clients AS headoffices "
+ "ON clients.headoffice = headoffices.id) "
+ "ON probs.wolink = wo.id) LEFT JOIN contracts ON clients.contract "
+ "= contracts.id) LEFT JOIN contracts "
+ "AS contracts_1 ON headoffices.contract = contracts_1.id "
+ "WHERE (((wo.id)=~WOID));";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=22));", q2);
executeQuery(q);
//------------------------------------------------
//QUICK COMPLETED FORMAT WORKORDER REPORT
q2 = "SELECT \"~REGTO\" AS zCompName, wo.id, wo.clientrefnum, "
+ "wo.clientcontact, clients.company AS clientcompany, "
+ "Format(wo.closed,\"Short Date\") AS wodate, "
+ "[users]![first] & \" \" & [users]![last] AS wotech, "
+ "labor.hours, labor.travhours, labor.nchours, rates_1.name "
+ "AS workrate, rates.name AS travrate, labor.start "
+ "AS starttime, labor.stop AS stoptime, wo.invoice, "
+ "labor.details, wo.onsite FROM (((users "
+ "RIGHT JOIN (labor RIGHT JOIN (wo LEFT JOIN probs ON "
+ "wo.id = probs.wolink) ON labor.link = probs.id) "
+ "ON users.id = labor.tech) LEFT JOIN clients ON wo.client "
+ "= clients.id) LEFT JOIN rates ON labor.travrate "
+ "= rates.id) LEFT JOIN rates AS rates_1 ON "
+ "labor.rate = rates_1.id WHERE (((wo.id)=~WOID)); ";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=0));", q2);
executeQuery(q);
//------------------------------------------------
//fixup the dispatch report recordset fields
executeQuery("UPDATE rptsmaster SET rptsmaster.recordset = \"wostandard\" "
+ "WHERE (((rptsmaster.id)=21));");
executeQuery("UPDATE rptsmaster SET rptsmaster.recordset = \"woquick\" "
+ "WHERE (((rptsmaster.id)=22));");
//Fix the workorder virtual names so they are
//the same as the original menu commands that called them
executeQuery("UPDATE rptsmaster SET rptsmaster.virtualname = \"&Dispatch format work order\" "
+ "WHERE (((rptsmaster.id)=22));");
executeQuery("UPDATE rptsmaster SET rptsmaster.virtualname = \"&Completed work order\" "
+ "WHERE (((rptsmaster.id)=0));");
executeQuery("UPDATE rptsmaster SET rptsmaster.virtualname = \"&Dispatch work order\" "
+ "WHERE (((rptsmaster.id)=21));");
executeQuery("UPDATE rptsmaster SET rptsmaster.virtualname = \"&Brief completed work order\" "
+ "WHERE (((rptsmaster.id)=1));");
executeQuery("UPDATE rptsmaster SET rptsmaster.virtualname = \"D&etailed completed work order\" "
+ "WHERE (((rptsmaster.id)=19));");
//Add the subreport filename field
executeQuery("ALTER TABLE rptsmaster ADD COLUMN subfilename TEXT(255);");
executeQuery("UPDATE rptsmaster SET rptsmaster.subfilename = \"wodispstpartssub.rpt\" "
+ "WHERE (((rptsmaster.id)=21));");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"164\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 164;
break;
}
case 164://changed to 165 on Oct 14th 2002 for version 1.9
{
//Add cost of parts to woparts
//this is used to record cost at time of use
executeQuery("ALTER TABLE woparts ADD COLUMN cost CURRENCY;");
executeQuery("ALTER TABLE woparts ALTER cost SET DEFAULT '0';");
executeQuery("UPDATE woparts SET woparts.cost = 0;");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"165\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 165;
break;
}
case 165://changed to 166 on Oct 14th 2002 for version 1.9
{
/*
Add new fields for rptsusers
long m_lPaperOrientation;
long m_lPaperSize;
long m_lPaperSource;
long m_lDuplexType;
*/
executeQuery("ALTER TABLE rptsusers ADD COLUMN porient INTEGER;");
executeQuery("ALTER TABLE rptsusers ALTER porient SET DEFAULT '0';");
executeQuery("UPDATE rptsusers SET rptsusers.porient = 0;");
executeQuery("ALTER TABLE rptsusers ADD COLUMN psize INTEGER;");
executeQuery("ALTER TABLE rptsusers ALTER psize SET DEFAULT '0';");
executeQuery("UPDATE rptsusers SET rptsusers.psize = 0;");
executeQuery("ALTER TABLE rptsusers ADD COLUMN psource INTEGER;");
executeQuery("ALTER TABLE rptsusers ALTER psource SET DEFAULT '0';");
executeQuery("UPDATE rptsusers SET rptsusers.psource = 0;");
executeQuery("ALTER TABLE rptsusers ADD COLUMN pduplex INTEGER;");
executeQuery("ALTER TABLE rptsusers ALTER pduplex SET DEFAULT '0';");
executeQuery("UPDATE rptsusers SET rptsusers.pduplex = 0;");
executeQuery("ALTER TABLE rptsusers DROP COLUMN devmode;");
executeQuery("ALTER TABLE rptsusers DROP COLUMN devmodesize;");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"166\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 166;
break;
}
case 166://changed to 167 on Oct 16th 2002 for version 1.9
{
//---------------------------------------
/*
Add new wodispst2.rpt
Dispatch work order (with labor)
*/
executeQuery("INSERT INTO rptsmaster ( virtualname, filename,subfilename, recordset, x, criteriafields, id ) "
+ "SELECT \"Dispatch work order (with &labor)\", \"wodispst2.rpt\", \"wodispstpartssub.rpt\", \"wostandard\", False, 32767, 40;");
q2 = "SELECT \"~ESTRATE\" AS estrate, \"~SCHEDTECH\" AS scheduledtech, "
+ "~ESTHOURS AS esthours, IIf(IsNull([clients].[company]),[clients].[last] "
+ "& \", \" & [clients].[first],[clients].[company]) "
+ "AS clientname, IIf(IsNull([clients].[streetaddress]),[clients].[mailaddress],[clients].[streetaddress]) "
+ "& IIf(IsNull([clients].[city]),\"\",Chr(13) "
+ "& [clients].[city]) & IIf(IsNull([clients].[stateprov]),\"\",\", "
+ "\" & [clients].[stateprov] "
+ "& IIf(IsNull([clients].[postal]),\"\",\" "
+ "\" & [clients].[postal])) AS address, \"Work required: "
+ "\" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) "
+ "& \"Unit: \" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] & \" (\" & [unitmodels].[model] "
+ "& \") SN: \" & [units].[sn],\"\") AS probheader, "
+ "\"~REGTO\" AS compname, wo.notes AS wonotes, "
+ "wo.clientrefnum, wo.clientcontact, wo.ourref, "
+ "wo.prob_reported, wo.prob_found, wo.action_taken, "
+ "wo.id AS wonumber, wo.created, labor.details, probs.notes, "
+ "IIf(IsNull(contracts_1.name),[contracts].[name],contracts_1.name) "
+ "AS contractinfo, IIf([probs].[unit]<>0,\"Unit: "
+ "\" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] & \" (\" "
+ "& [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") "
+ "AS Equipment, wo.starttime AS [Booked for], wo.stoptime "
+ "AS [Booked for END], wo.closed, probs.brief, "
+ "clients.mailaddress, clients.streetaddress, clients.city, "
+ "clients.stateprov, clients.postal, clients.country, "
+ "clients.bizphone, clients.extension, "
+ "clients.technotes, clients.first, clients.last, probstat.notes "
+ "AS STATUS, probstat_1.notes AS ITEMSTATUS, "
+ "projects.name AS PROJNAME, projects.notes AS "
+ "PROJNOTES, users.first, users.last, users.initials, "
+ "labor.hours, labor.nchours, labor.travhours, labor.start, "
+ "labor.stop, labor.distance FROM (projects "
+ "RIGHT JOIN ((((((labor RIGHT JOIN (((units RIGHT "
+ "JOIN probs ON units.id = probs.unit) LEFT JOIN "
+ "unitmodels ON units.model = unitmodels.id) LEFT JOIN "
+ "nonclients ON unitmodels.manufacturer = nonclients.id) "
+ "ON labor.link = probs.id) RIGHT JOIN ((wo "
+ "LEFT JOIN clients ON wo.client = clients.id) LEFT "
+ "JOIN clients AS headoffices ON clients.headoffice "
+ "= headoffices.id) ON probs.wolink = wo.id) LEFT JOIN "
+ "contracts ON clients.contract = contracts.id) LEFT "
+ "JOIN contracts AS contracts_1 ON headoffices.contract "
+ "= contracts_1.id) LEFT JOIN probstat ON wo.status "
+ "= probstat.id) LEFT JOIN probstat AS probstat_1 "
+ "ON probs.status = probstat_1.id) ON projects.id "
+ "= wo.project) LEFT JOIN users ON labor.tech = "
+ "users.id WHERE (((wo.id)=~WOID));";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=40));", q2);
executeQuery(q);
//subreport query
q2 = "SELECT probs.id, woparts.quantity, woparts.sn, parts.partnumber, "
+ "woparts.misc, parts.description, parts.retail, "
+ "IIf(IsNull(woparts.misc),[parts].[partnumber] "
+ "& \" \" & [parts].[description] & \": \" & [woparts].[quantity] "
+ "& \" @ \" & Format([woparts].[price],\"Currency\") "
+ "& \" ea.\" & IIf(IsNull([woparts].[sn]),\"\",\" "
+ "sn:\" & [woparts].[sn]) , [woparts].[misc] "
+ "& \": \" & [woparts].[quantity] & \" @ \" & Format([woparts].[price],\"Currency\") "
+ "& \" ea.sn:\" & [woparts].[sn] "
+ ") AS item, [woparts].[quantity]*[woparts].[price] "
+ "AS linetotal FROM (woparts RIGHT JOIN (probs "
+ "RIGHT JOIN wo ON probs.wolink = wo.id) ON woparts.link "
+ "= probs.id) LEFT JOIN parts ON woparts.partnum "
+ "= parts.id WHERE (((woparts.quantity) Is Not Null) "
+ "AND ((wo.id)=~WOID));";
q = string.Format("UPDATE rptsmaster SET rptsmaster.subreportquery = '%s' "
+ "WHERE (((rptsmaster.id)=40));", q2);
executeQuery(q);
//------------------------------------------------
//---------- ADD DISPLAY ORDER FIELD TO RPTS MASTER
executeQuery("ALTER TABLE rptsmaster ADD COLUMN wodisplayorder INTEGER;");
executeQuery("UPDATE rptsmaster SET rptsmaster.wodisplayorder = 1 "
+ "WHERE (((rptsmaster.virtualname)=\"&Dispatch format work order\"));");
executeQuery("UPDATE rptsmaster SET rptsmaster.wodisplayorder = 2 "
+ "WHERE (((rptsmaster.virtualname)=\"&Completed work order\"));");
executeQuery("UPDATE rptsmaster SET rptsmaster.wodisplayorder = 3 "
+ "WHERE (((rptsmaster.virtualname)=\"&Dispatch work order\"));");
executeQuery("UPDATE rptsmaster SET rptsmaster.wodisplayorder = 4 "
+ "WHERE (((rptsmaster.virtualname)=\"Dispatch work order (with &labor)\"));");
executeQuery("UPDATE rptsmaster SET rptsmaster.wodisplayorder = 5 "
+ "WHERE (((rptsmaster.virtualname)=\"&Brief completed work order\"));");
executeQuery("UPDATE rptsmaster SET rptsmaster.wodisplayorder = 6 "
+ "WHERE (((rptsmaster.virtualname)=\"D&etailed completed work order\"));");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"167\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 167;
break;
}
case 167:
{
//Addition of client address book Report
executeQuery("INSERT INTO rptsmaster ( virtualname, filename, recordset, x, criteriafields, id ) "
+ "SELECT \"Client address book\", \"clntadd.rpt\", \"mgr\", True, 32767, 41;");
q2 = "SELECT clients.*, HEADS.* FROM clients LEFT JOIN clients "
+ "AS HEADS ON clients.headoffice = HEADS.id WHERE "
+ "(((clients.id)~CUST)) ORDER BY clients.company; ";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=41));", q2);
executeQuery(q);
//Set STATUS column in statusviews to -1 if it was previously 1 as -1
//now means any status where 1 used to mean any status
q = string.Format("UPDATE statusviews SET statusviews.status = -1 WHERE (((statusviews.status)=1));");
executeQuery(q);
//10/26/2002
//set assigntech to correct value on old quick work orders
q = "UPDATE labor RIGHT JOIN (probs RIGHT JOIN wo ON probs.wolink = wo.id) "
+ "ON labor.link = probs.id SET wo.assigntech = [labor].[tech] "
+ "WHERE (((wo.quick)=True) AND ((wo.assigntech)=-1));";
executeQuery(q);
//Add aras_client field to rptsmaster
executeQuery("ALTER TABLE rptsmaster ADD COLUMN client_aras YESNO;");
executeQuery("ALTER TABLE rptsmaster ALTER client_aras SET DEFAULT '-1';");
executeQuery("UPDATE rptsmaster SET rptsmaster.client_aras = True;");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"168\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.0.0\";");
nVersion = 168;
//buptodate=true;//<<==========CRITICAL!!!!!, should be at end of last case statement only
break;
}
case 168:
{
//Changed .......
executeQuery("ALTER TABLE probs ALTER COLUMN meter INTEGER;");
//Update report 31 to fix bug in query causing part descriptions to be blank
q2 = "SELECT ~CRITERIA AS CRITERIA, wo.id AS wonumber, \"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & \"Unit: \" & [nonclients].[company_person] & \" \" & [unitmodels].[description] & \" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") AS probheader, \"test\" AS compname, wom.probnum, wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, wom.PROBBRIEF, wom.item, wom.linetotal, wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created AS wodate, wo.invoice, wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, clients.bizphone, clients.extension, clients.fax, clients.email, clients.company AS clientname, wom.details, wom.START, wom.STOP,wom.REGHOURS, wom.TRAVHOURS, wom.NCHOURS, projects.name AS PROJNAME, projects.notes AS PROJNOTES, wo.client, wo.project, wo.type, wotypes.category AS CATEGORY "
+ "FROM ((((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT \"1\" AS ctype, rates.partnum AS LABPN, travelrates.partnum AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink AS wonum, \"Service: \" & users.first & \" \" & users.last & \" - \" & IIf(labor.hours>0,labor.hours & \" hrs. @ \" & Format(rates.rate,\"Currency\") & \" (pn:\" & rates.partnum & \")\",\"\") & IIf(labor.nchours>0,\" (\" & labor.nchours & \" hrs. no charge)\",\"\") & IIf(labor.travhours>0,\", \" & labor.travhours & \" hrs. travel @ \" & Format(travelrates.rate,\"Currency\") & IIf(IsNull(travelrates.partnum),\"\",\" (pn:\" & travelrates.partnum & \")\"),\"\") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) AS linetotal, labor.details , labor.start AS START, labor.stop AS STOP, labor.hours AS REGHOURS, labor.travhours AS TRAVHOURS, labor.nchours as NCHOURS FROM users INNER JOIN (rates AS travelrates INNER JOIN (rates INNER JOIN (labor INNER JOIN probs ON labor.link = probs.id) ON rates.id = labor.rate) ON travelrates.id = labor.travrate) ON users.id = labor.tech WHERE (((probs.wolink)<>0)) "
+ " UNION ALL (SELECT \"2\" AS ctype, \"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, \"Parts: \" & IIf(IsNull(woparts.misc), parts.partnumber & \" \" & parts.description & \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\") & \" ea.(sn:\" & woparts.sn & \")\",woparts.misc & \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\")& \" ea.(sn:\" & woparts.sn & \")\") AS item, woparts.quantity*woparts.price AS linetotal, \"\" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM (probs INNER JOIN woparts ON probs.id = woparts.link) "
+ " LEFT JOIN parts ON woparts.partnum = parts.id WHERE (((probs.wolink)<>0)) "
+ "UNION ALL (SELECT \"3\" AS ctype,\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, \"Third party service: \" AS item, subrepair.charge AS linettotal, \"\" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM probs INNER JOIN subrepair ON probs.id = subrepair.link WHERE (((probs.wolink)<>0) AND ((subrepair.charge)<>0))))]. AS wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.id = wom.probnum) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN projects ON wo.project = projects.id ) LEFT JOIN wotypes ON wo.type = wotypes.id "
+ "WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_)) "
+ "ORDER BY wo.id;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=31));", q2);
executeQuery(q);
//2023-07-14 migrate to v8 going to skip this stuff hopefully don't need it
// //************* change any client aras logins that match ayanova user logins **********
// CString strReportDupes,str;
// CString sLoginARAS,sLoginARASHash,sCompany,sNewLogin;
// GZK k;
// long l=0;
// long lARASID=0;
// //check for and zap any aras client login names that match AyaNova login names
// //loop through all aras login names
// //for each that hashes out to same as ayanova login name, change login name to prepend unique string
// q="SELECT clients.company AS COMP1, clients_aras.loginid, "
// "clients_aras.id AS ARASID FROM clients_aras LEFT "
// "JOIN clients ON clients_aras.clientlink = clients.id "
// "ORDER BY clients.company, clients_aras.loginid;";
// rs2->QueryReadOnly(q);
// if(!rs2->IsEmpty())
// {
// do{
// rs2->FetchField("loginid",&sLoginARAS);
// sLoginARASHash=sLoginARAS;
// k.GZHash(&sLoginARASHash);
// q=string.Format("SELECT users.login FROM users WHERE (((users.login)=\"%s\"));",sLoginARASHash);
// rs3->QueryReadOnly(q);
// if(!rs3->IsEmpty())//This ARAS client login matches AyaNova login
// {
// l++;
// sNewLogin.Format("Temporary%u",l);
// rs2->FetchField("COMP1",&sCompany);
// str.Format("Client %s ARAS login account %s changed to %s\r\n",sCompany,sLoginARAS,sNewLogin);
// strReportDupes+=str;
// rs2->FetchField("ARASID",&lARASID);
// q=string.Format("UPDATE clients_aras SET clients_aras.loginid = \"%s\" "
// "WHERE (((clients_aras.id) = %u ));",sNewLogin,lARASID);
// executeQuery(q);
// }
// }while(rs2->MoveForward());
// }
// //Check for duplicate ARAS client login names
// q="SELECT clients_aras.loginid, clients_aras.id AS ARASID, clients.company AS COMP1 "
//"FROM clients_aras LEFT JOIN clients ON "
//"clients_aras.clientlink = clients.id WHERE (((clients_aras.loginid) "
//"In (SELECT [loginid] FROM [clients_aras] "
//"As Tmp GROUP BY [loginid] HAVING Count(*)>1 "
//"))) ORDER BY clients_aras.loginid;";
// rs2->QueryReadOnly(q);
// if(!rs2->IsEmpty())//there are dupes
// {
// do{
// l++;
// sNewLogin.Format("Temporary%u",l);
// rs2->FetchField("COMP1",&sCompany);
// str.Format("Client %s ARAS login account %s changed to %s\r\n",sCompany,sLoginARAS,sNewLogin);
// strReportDupes+=str;
// rs2->FetchField("ARASID",&lARASID);
// q=string.Format("UPDATE clients_aras SET clients_aras.loginid = \"%s\" "
// "WHERE (((clients_aras.id) = %u ));",sNewLogin,lARASID);
// executeQuery(q);
// }while(rs2->MoveForward());
// }
// if(!strReportDupes.IsEmpty())
// {
// strReportDupes=
// "******** READ THIS CAREFULLY ********\r\n"
// "The following client(s) ARAS login ID's we're changed for security reasons.\r\n\r\n"
// "You should save this information now and change them after the update has\r\n"
// "completed. (otherwise they will not be able to log in)\r\n\r\n"
// "To save this information click on the copy all text.. button, open a word processor or\r\n"
// "Microsoft Notepad and paste. You can then save / print this list.\r\n"
// "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\r\n"+strReportDupes;
// m_pApp->ShowStuff(strReportDupes);
// strReportDupes.Empty();
// }
//******************************************************************************
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"169\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.3.0\";");
nVersion = 169;
//buptodate=true;//<<==========CRITICAL!!!!!, should be at end of last case statement only
break;
}
case 169:
{
//------------------------------------------------
//REMOVE Hrs. FROM WORK ORDER REPORT QUERIES
//STANDARD BRIEF
q2 = "SELECT \"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) "
+ "& \"Unit: \" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] "
+ "& \" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") "
+ "AS probheader, \"~REGTO\" AS compname, wom.probnum, "
+ "wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, "
+ "wom.PROBBRIEF, wom.item, wom.linetotal, "
+ "wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created "
+ "AS wodate, wo.id AS wonumber, wo.invoice, "
+ "wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, "
+ "clients.billheadoffice, clients.first, clients.last, "
+ "clients.mailaddress, clients.streetaddress, "
+ "clients.city, clients.stateprov, clients.postal, "
+ "clients.country, clients.bizphone, clients.extension, "
+ "clients.fax, clients.email, [clients].[company] "
+ "AS clientname, IIf(IsNull([headoffices].[company]),[headoffices].[last] "
+ "& \", \" & [headoffices].[first],[headoffices].[company]) "
+ "AS headname, wom.details, "
+ "wom.STARTDATE, wom.STOPDATE, projects.name "
+ "AS PROJNAME, projects.notes AS PROJNOTES FROM "
+ "(((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT "
+ "\"1\" AS ctype, rates.partnum AS LABPN, travelrates.partnum "
+ "AS TRAVPN, probs.notes AS PROBNOTES, probs.brief "
+ "AS PROBBRIEF, probs.id AS probnum, probs.wolink "
+ "AS wonum, \"Service: \" & users.first & \" \" & "
+ "users.last & \" - \" & IIf(labor.hours>0,labor.hours "
+ "& \" @ \" & Format(rates.rate,\"Currency\") & \" "
+ "(pn:\" & rates.partnum & \")\",\"\") & IIf(labor.nchours>0,\" "
+ "(\" & labor.nchours & \" no charge)\",\"\") "
+ "& IIf(labor.travhours>0,\", \" & labor.travhours & \" "
+ "travel @ \" & Format(travelrates.rate,\"Currency\") "
+ "& IIf(IsNull(travelrates.partnum),\"\",\" (pn:\" "
+ "& travelrates.partnum & \")\"),\"\") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) "
+ "AS linetotal,labor.details, labor.start AS STARTDATE, "
+ "labor.stop as STOPDATE FROM users INNER JOIN "
+ "(rates AS travelrates INNER JOIN (rates INNER JOIN "
+ "(labor INNER JOIN probs ON labor.link = probs.id) "
+ "ON rates.id = labor.rate) ON travelrates.id = labor.travrate) "
+ "ON users.id = labor.tech WHERE (((probs.wolink)=~WOID)) "
+ "UNION ALL (SELECT \"2\" AS ctype, "
+ "\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, "
+ "probs.brief AS PROBBRIEF, probs.id AS probnum, "
+ " probs.wolink as wonum, \"Parts: \" & IIf(IsNull(woparts.misc),parts.partnumber "
+ "& \" \" & parts.description "
+ "& \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\") "
+ "& \" ea.(sn:\" & woparts.sn "
+ "& \")\",woparts.misc & \": \" & woparts.quantity "
+ "& \" @ \" & Format(woparts.price,\"Currency\")& \" ea.(sn:\" "
+ "& woparts.sn & \")\") AS item, woparts.quantity*woparts.price "
+ "AS linetotal,\"\" AS details, #03/12/1968# "
+ "AS STARTDATE, #03/12/1968# AS STOPDATE FROM "
+ "(probs INNER JOIN woparts ON probs.id = woparts.link) "
+ "LEFT JOIN parts ON woparts.partnum = parts.id "
+ "WHERE (((probs.wolink)=~WOID)) UNION ALL (SELECT "
+ " \"3\" AS ctype,\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes "
+ "AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id "
+ "AS probnum, probs.wolink as wonum, \"Third party "
+ "service: \" AS item, subrepair.charge AS linettotal, "
+ "\"\" AS details, #03/12/1968# AS STARTDATE, #03/12/1968# "
+ "AS STOPDATE FROM probs INNER JOIN subrepair "
+ "ON probs.id = subrepair.link WHERE (((probs.wolink)=~WOID) "
+ "AND ((subrepair.charge)<>0))))]. AS "
+ "wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN "
+ "clients ON wo.client = clients.id) LEFT JOIN clients "
+ "AS headoffices ON clients.headoffice = headoffices.id) "
+ "ON probs.id = wom.probnum) ON units.id = probs.unit) "
+ "LEFT JOIN unitmodels ON units.model = unitmodels.id) "
+ "LEFT JOIN nonclients ON unitmodels.manufacturer "
+ "= nonclients.id) LEFT JOIN projects ON "
+ "wo.project = projects.id ORDER BY wom.STARTDATE DESC; ";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=1));", q2);
executeQuery(q);
//------------------------------------------------
//STANDARD COMPLETED
q2 = "SELECT \"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) "
+ "& \"Unit: \" & [nonclients].[company_person] "
+ "& \" \" & [unitmodels].[description] "
+ "& \" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") "
+ "AS probheader, \"~REGTO\" AS compname, wom.probnum, "
+ "wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, "
+ "wom.PROBBRIEF, wom.item, wom.linetotal, "
+ "wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created "
+ "AS wodate, wo.id AS wonumber, wo.invoice, "
+ "wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, "
+ "clients.billheadoffice, clients.first, clients.last, "
+ "clients.mailaddress, clients.streetaddress, "
+ "clients.city, clients.stateprov, clients.postal, "
+ "clients.country, clients.bizphone, clients.extension, "
+ "clients.fax, clients.email, [clients].[company] "
+ "AS clientname, IIf(IsNull([headoffices].[company]),[headoffices].[last] "
+ "& \", \" & [headoffices].[first],[headoffices].[company]) "
+ "AS headname, wom.details, "
+ "wom.STARTDATE, wom.STOPDATE, projects.name "
+ "AS PROJNAME, projects.notes AS PROJNOTES FROM "
+ "(((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT "
+ "\"1\" AS ctype, rates.partnum AS LABPN, travelrates.partnum "
+ "AS TRAVPN, probs.notes AS PROBNOTES, probs.brief "
+ "AS PROBBRIEF, probs.id AS probnum, probs.wolink "
+ "AS wonum, \"Service: \" & users.first & \" \" & "
+ "users.last & \" - \" & IIf(labor.hours>0,labor.hours "
+ "& \" @ \" & Format(rates.rate,\"Currency\") & \" "
+ "(pn:\" & rates.partnum & \")\",\"\") & IIf(labor.nchours>0,\" "
+ "(\" & labor.nchours & \" no charge)\",\"\") "
+ "& IIf(labor.travhours>0,\", \" & labor.travhours & \" "
+ "travel @ \" & Format(travelrates.rate,\"Currency\") "
+ "& IIf(IsNull(travelrates.partnum),\"\",\" (pn:\" "
+ "& travelrates.partnum & \")\"),\"\") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) "
+ "AS linetotal,labor.details, labor.start AS STARTDATE, "
+ "labor.stop as STOPDATE FROM users INNER JOIN "
+ "(rates AS travelrates INNER JOIN (rates INNER JOIN "
+ "(labor INNER JOIN probs ON labor.link = probs.id) "
+ "ON rates.id = labor.rate) ON travelrates.id = labor.travrate) "
+ "ON users.id = labor.tech WHERE (((probs.wolink)=~WOID)) "
+ "UNION ALL (SELECT \"2\" AS ctype, "
+ "\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, "
+ "probs.brief AS PROBBRIEF, probs.id AS probnum, "
+ " probs.wolink as wonum, \"Parts: \" & IIf(IsNull(woparts.misc),parts.partnumber "
+ "& \" \" & parts.description "
+ "& \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\") "
+ "& \" ea.(sn:\" & woparts.sn "
+ "& \")\",woparts.misc & \": \" & woparts.quantity "
+ "& \" @ \" & Format(woparts.price,\"Currency\")& \" ea.(sn:\" "
+ "& woparts.sn & \")\") AS item, woparts.quantity*woparts.price "
+ "AS linetotal,\"\" AS details, #03/12/1968# "
+ "AS STARTDATE, #03/12/1968# AS STOPDATE FROM "
+ "(probs INNER JOIN woparts ON probs.id = woparts.link) "
+ "LEFT JOIN parts ON woparts.partnum = parts.id "
+ "WHERE (((probs.wolink)=~WOID)) UNION ALL (SELECT "
+ " \"3\" AS ctype,\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes "
+ "AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id "
+ "AS probnum, probs.wolink as wonum, \"Third party "
+ "service: \" AS item, subrepair.charge AS linettotal, "
+ "\"\" AS details, #03/12/1968# AS STARTDATE, #03/12/1968# "
+ "AS STOPDATE FROM probs INNER JOIN subrepair "
+ "ON probs.id = subrepair.link WHERE (((probs.wolink)=~WOID) "
+ "AND ((subrepair.charge)<>0))))]. AS "
+ "wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN "
+ "clients ON wo.client = clients.id) LEFT JOIN clients "
+ "AS headoffices ON clients.headoffice = headoffices.id) "
+ "ON probs.id = wom.probnum) ON units.id = probs.unit) "
+ "LEFT JOIN unitmodels ON units.model = unitmodels.id) "
+ "LEFT JOIN nonclients ON unitmodels.manufacturer "
+ "= nonclients.id) LEFT JOIN projects ON "
+ "wo.project = projects.id ORDER BY wom.STARTDATE DESC; ";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=19));", q2);
executeQuery(q);
//------------------------------------------------
//WORKORDERS BRIEF
q2 = "SELECT ~CRITERIA AS CRITERIA, wo.id AS wonumber, \"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & \"Unit: \" & [nonclients].[company_person] & \" \" & [unitmodels].[description] & \" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") AS probheader, \"test\" AS compname, wom.probnum, wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, wom.PROBBRIEF, wom.item, wom.linetotal, wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created AS wodate, wo.invoice, wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, clients.bizphone, clients.extension, clients.fax, clients.email, clients.company AS clientname, wom.details, wom.START, wom.STOP,wom.REGHOURS, wom.TRAVHOURS, wom.NCHOURS, projects.name AS PROJNAME, projects.notes AS PROJNOTES, wo.client, wo.project, wo.type, wotypes.category AS CATEGORY "
+ "FROM ((((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT \"1\" AS ctype, rates.partnum AS LABPN, travelrates.partnum AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink AS wonum, \"Service: \" & users.first & \" \" & users.last & \" - \" & IIf(labor.hours>0,labor.hours & \" @ \" & Format(rates.rate,\"Currency\") & \" (pn:\" & rates.partnum & \")\",\"\") & IIf(labor.nchours>0,\" (\" & labor.nchours & \" no charge)\",\"\") & IIf(labor.travhours>0,\", \" & labor.travhours & \" travel @ \" & Format(travelrates.rate,\"Currency\") & IIf(IsNull(travelrates.partnum),\"\",\" (pn:\" & travelrates.partnum & \")\"),\"\") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) AS linetotal, labor.details , labor.start AS START, labor.stop AS STOP, labor.hours AS REGHOURS, labor.travhours AS TRAVHOURS, labor.nchours as NCHOURS FROM users INNER JOIN (rates AS travelrates INNER JOIN (rates INNER JOIN (labor INNER JOIN probs ON labor.link = probs.id) ON rates.id = labor.rate) ON travelrates.id = labor.travrate) ON users.id = labor.tech WHERE (((probs.wolink)<>0)) "
+ "UNION ALL (SELECT \"2\" AS ctype, \"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, \"Parts: \" & IIf(woparts.misc= \"\",parts.partnumber & \" \" & parts.description & \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\") & \" ea.(sn:\" & woparts.sn & \")\",woparts.misc & \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\")& \" ea.(sn:\" & woparts.sn & \")\") AS item, woparts.quantity*woparts.price AS linetotal, \"\" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM (probs INNER JOIN woparts ON probs.id = woparts.link) "
+ "LEFT JOIN parts ON woparts.partnum = parts.id WHERE (((probs.wolink)<>0)) "
+ "UNION ALL (SELECT \"3\" AS ctype,\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, \"Third party service: \" AS item, subrepair.charge AS linettotal, \"\" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM probs INNER JOIN subrepair ON probs.id = subrepair.link WHERE (((probs.wolink)<>0) AND ((subrepair.charge)<>0))))]. AS wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.id = wom.probnum) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN projects ON wo.project = projects.id ) LEFT JOIN wotypes ON wo.type = wotypes.id "
+ "WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_)) "
+ "ORDER BY wo.id;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=32));", q2);
executeQuery(q);
//WORKORDERS DETAILED
q2 = "SELECT ~CRITERIA AS CRITERIA, wo.id AS wonumber, \"Work required: \" & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & \"Unit: \" & [nonclients].[company_person] & \" \" & [unitmodels].[description] & \" (\" & [unitmodels].[model] & \") SN: \" & [units].[sn],\"\") AS probheader, \"test\" AS compname, wom.probnum, wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, wom.PROBBRIEF, wom.item, wom.linetotal, wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created AS wodate, wo.invoice, wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, clients.bizphone, clients.extension, clients.fax, clients.email, clients.company AS clientname, wom.details, wom.START, wom.STOP,wom.REGHOURS, wom.TRAVHOURS, wom.NCHOURS, projects.name AS PROJNAME, projects.notes AS PROJNOTES, wo.client, wo.project, wo.type, wotypes.category AS CATEGORY "
+ "FROM ((((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT \"1\" AS ctype, rates.partnum AS LABPN, travelrates.partnum AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink AS wonum, \"Service: \" & users.first & \" \" & users.last & \" - \" & IIf(labor.hours>0,labor.hours & \" @ \" & Format(rates.rate,\"Currency\") & \" (pn:\" & rates.partnum & \")\",\"\") & IIf(labor.nchours>0,\" (\" & labor.nchours & \" no charge)\",\"\") & IIf(labor.travhours>0,\", \" & labor.travhours & \" travel @ \" & Format(travelrates.rate,\"Currency\") & IIf(IsNull(travelrates.partnum),\"\",\" (pn:\" & travelrates.partnum & \")\"),\"\") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) AS linetotal, labor.details , labor.start AS START, labor.stop AS STOP, labor.hours AS REGHOURS, labor.travhours AS TRAVHOURS, labor.nchours as NCHOURS FROM users INNER JOIN (rates AS travelrates INNER JOIN (rates INNER JOIN (labor INNER JOIN probs ON labor.link = probs.id) ON rates.id = labor.rate) ON travelrates.id = labor.travrate) ON users.id = labor.tech WHERE (((probs.wolink)<>0)) "
+ " UNION ALL (SELECT \"2\" AS ctype, \"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, \"Parts: \" & IIf(IsNull(woparts.misc), parts.partnumber & \" \" & parts.description & \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\") & \" ea.(sn:\" & woparts.sn & \")\",woparts.misc & \": \" & woparts.quantity & \" @ \" & Format(woparts.price,\"Currency\")& \" ea.(sn:\" & woparts.sn & \")\") AS item, woparts.quantity*woparts.price AS linetotal, \"\" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM (probs INNER JOIN woparts ON probs.id = woparts.link) "
+ " LEFT JOIN parts ON woparts.partnum = parts.id WHERE (((probs.wolink)<>0)) "
+ "UNION ALL (SELECT \"3\" AS ctype,\"na\" AS LABPN, \"na\" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, \"Third party service: \" AS item, subrepair.charge AS linettotal, \"\" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM probs INNER JOIN subrepair ON probs.id = subrepair.link WHERE (((probs.wolink)<>0) AND ((subrepair.charge)<>0))))]. AS wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.id = wom.probnum) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN projects ON wo.project = projects.id ) LEFT JOIN wotypes ON wo.type = wotypes.id "
+ "WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_)) "
+ "ORDER BY wo.id;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=31));", q2);
executeQuery(q);
//=-=-=-=- PM by UNIT bug fix
q2 = "SELECT ~CRITERIA AS CRITERIA, pmhead.id, nonclients.company_person "
+ "AS MAKE, unitmodels.model, units.sn, "
+ "unitmodels.description AS UNITDESC, units.id1, "
+ "units.id2, units.id3, clients.company, nonclients.phone, "
+ "pmhead.description, pmhead.notes, Min(pmitems.schedate) "
+ "AS nextsrvdate, Min(pmitems.schedmeter) "
+ "AS nextsrvmeter, units.lastmeter, pmhead.woconvert, "
+ "pmhead.repeatevery AS rptmonths_count, pmhead.rptweeks, "
+ "pmhead.rptdays, units.purchasedate, pmhead.ageyears, "
+ "pmhead.agemonths, users.initials, users.first, "
+ "users.last, pmhead.isclient FROM pmitems "
+ "RIGHT JOIN (((pmhead LEFT JOIN ((units LEFT JOIN "
+ "unitmodels ON units.model = unitmodels.id) LEFT JOIN "
+ "nonclients ON unitmodels.manufacturer = nonclients.id) "
+ "ON pmhead.link = units.id) LEFT JOIN clients "
+ "ON units.client = clients.id) LEFT JOIN users "
+ "ON pmhead.tech = users.id) ON pmitems.pmschedlink "
+ "= pmhead.id GROUP BY ~CRITERIA , pmhead.id, nonclients.company_person, "
+ "unitmodels.model, units.sn, unitmodels.description, "
+ "units.id1, units.id2, units.id3, "
+ "clients.company, nonclients.phone, pmhead.description, "
+ "pmhead.notes, units.lastmeter, pmhead.woconvert, "
+ "pmhead.repeatevery, pmhead.rptweeks, pmhead.rptdays, "
+ "units.purchasedate, pmhead.ageyears, "
+ "pmhead.agemonths, users.initials, users.first, users.last, "
+ "pmhead.isclient, units.client, units.id, "
+ "units.model HAVING (((pmhead.isclient)=False) AND "
+ "((units.client)~CUST) AND ((units.id)~UNIT) AND ((units.model)~MODL)) "
+ "ORDER BY nonclients.company_person, "
+ "unitmodels.model, units.sn, clients.company;";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=34));", q2);
executeQuery(q);
//------------------------------------------------
//---------------------------------------
//P.M. by client bug fix
q2 = "SELECT ~CRITERIA AS CRITERIA, [clients].[company], "
+ "[pmhead].[description], [pmhead].[notes], Min([pmitems].[schedate]) "
+ "AS nextsrvdate, [pmhead].[link] "
+ "AS CLIENTID, [pmhead].[woconvert], [pmhead].[repeatevery] "
+ "AS rptmonths_count, [pmhead].[rptweeks], [pmhead].[rptdays], "
+ "[pmhead].[ageyears], [pmhead].[agemonths], "
+ "[users].[initials], [users].[first] AS "
+ "TECH_FIRST, [users].[last] AS TECH_LAST FROM pmitems "
+ "RIGHT JOIN (clients RIGHT JOIN (pmhead LEFT JOIN "
+ "users ON [pmhead].[tech]=[users].[id]) ON [clients].[id]=[pmhead].[link]) "
+ "ON [pmitems].[pmschedlink]=[pmhead].[id] "
+ "GROUP BY ~CRITERIA, [clients].[company], "
+ "[pmhead].[description], [pmhead].[notes], "
+ "[pmhead].[link], [pmhead].[woconvert], [pmhead].[repeatevery], "
+ "[pmhead].[rptweeks], [pmhead].[rptdays], "
+ "[pmhead].[ageyears], [pmhead].[agemonths], [users].[initials], "
+ "[users].[first], [users].[last], "
+ "[pmhead].[isclient] HAVING (((Min(pmitems.schedate)) "
+ "Between #~STRT# And #~END_#) AND ((pmhead.link)~CUST) "
+ "AND ((pmhead.isclient)=True)) ORDER BY [clients].[company]; ";
q = string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
+ "WHERE (((rptsmaster.id)=35));", q2);
executeQuery(q);
//------------------------------------------------
//2023-07-14 migrate to v8 skipping this hopefully unneeded block
// CString strID,strName,strRights,strHash;
// long lData;
// GZK k;
// //Add new ARAS REQUEST right:
// //loop through all the group records
// rs->Query("SELECT groups.* FROM groups;");
// do{
// rs->FetchField("id",&lData);
// rs->FetchField("a",&strName);
// rs->FetchField("b",&strRights);
// //decrypt the original rights string
// k.GZDecrypt(&strRights,false);
// strRights=strRights+"1";//add on the extra right for schedule view
// //re-'crypt'
// k.GZEncrypt(&strRights,false);
// //HASH
// strHash.Format("%u%s%s",lData,strName,strRights);
// k.GZHash(&strHash);
// //save the rights
// rs->UpdateField("b",&strRights);
// //save the hash
// rs->UpdateField("c",&strHash);
// //save the record
// if(!rs->SaveRecord())
// AfxMessageBox("Error trying to save rights record");
// }while(rs->MoveForward());
// //Remove any bogus sub-repair records that were created by ARAS with a "where" set to zero
// //change has already been made in ARAS 2.9.3 that will prevent them from being created in future
// executeQuery("DELETE subrepair.*, subrepair.where FROM subrepair WHERE (((subrepair.where)=0));");
// //remove any blank customer service requests
// executeQuery("DELETE client_requests.workorderid, client_requests.request, "
//"client_requests.probdetails, client_requests.refnum, "
//"client_requests.requestor_id, client_requests.* "
//"FROM client_requests WHERE (((client_requests.workorderid)=0) "
//"AND ((client_requests.request) "
//"Is Null) AND ((client_requests.probdetails) Is "
//"Null) AND ((client_requests.refnum) Is Null)); ");
// //Add the UNIT id to the ARAS requests table
// //Add aras_client field to rptsmaster
// executeQuery("ALTER TABLE client_requests ADD COLUMN unitid INTEGER;");
// executeQuery("ALTER TABLE client_requests ALTER unitid SET DEFAULT '0';");
// executeQuery("UPDATE client_requests SET client_requests.unitid = 0;");
// //add a field to defaults to control what client sees in ARAS workorder screen
// executeQuery("ALTER TABLE defaults ADD COLUMN limit_client_aras YESNO;");
// executeQuery("UPDATE defaults SET defaults.limit_client_aras = False;");
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"170\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.4.0\";");
nVersion = 170;
//buptodate=true;//<<==========CRITICAL!!!!!, should be at end of last case statement only
break;
}
case 170:
{ //v1.9.4.4 changes
//Fix client address book report criteria fields
executeQuery("UPDATE rptsmaster SET rptsmaster.criteriafields = 129 "
+ "WHERE (((rptsmaster.id)=41));");
//Fix technician billing summary report criteria fields
executeQuery("UPDATE rptsmaster SET rptsmaster.criteriafields = 911 "
+ "WHERE (((rptsmaster.id)=17));");
executeQuery("UPDATE wo SET wo.modified = [wo].[created], wo.modifier "
+ "= [wo].[creator] WHERE (((wo.modified) Is Null)); ");
/*
q2="SELECT clients.*, HEADS.* FROM clients LEFT JOIN clients "
"AS HEADS ON clients.headoffice = HEADS.id WHERE "
"(((clients.id)~CUST)) ORDER BY clients.company; ";
q=string.Format("UPDATE rptsmaster SET rptsmaster.query = '%s' "
"WHERE (((rptsmaster.id)=41));",q2);
executeQuery(q);
//Set STATUS column in statusviews to -1 if it was previously 1 as -1
//now means any status where 1 used to mean any status
q=string.Format("UPDATE statusviews SET statusviews.status = -1 WHERE (((statusviews.status)=1));");
executeQuery(q);
//10/26/2002
//set assigntech to correct value on old quick work orders
q="UPDATE labor RIGHT JOIN (probs RIGHT JOIN wo ON probs.wolink = wo.id) "
"ON labor.link = probs.id SET wo.assigntech = [labor].[tech] "
"WHERE (((wo.quick)=True) AND ((wo.assigntech)=-1));";
executeQuery(q);
*/
//fix the version number up
executeQuery("UPDATE defaults SET defaults.versioninfo = \"171\";");
executeQuery("UPDATE defaults SET defaults.min_exe = \"1.9.4.0\";");
nVersion = 171;
buptodate = true;//<<==========CRITICAL!!!!!, should be at end of last case statement only
break;
}
//<--------Insert new version before here --------------------
default:
{
throw new Exception(
"Your database version was not recognized and can not be updated automatically\r\n"
+ "Please email support@ayanova.com immediately with this version number: %i\r\n" + nVersion);
//AfxMessageBox(msg);
return;
}
break;
}
}//while not buptodate
////rs->Close();
//AfxMessageBox("You should close and re-start AyaNova now\r\n\r\n OR \r\n\r\n"
// "follow any instructions given during the update\r\n"
// "as directed before restarting");
}
}
#endregion upgrade ce
}
Reference in New Issue View Git Blame Copy Permalink