private bool buildPath1_Month(ref string lPath, ref string lServerPath)
{
bool functionReturnValue = false;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table tbl = new ADOX.Table();
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Connection cn = default(ADODB.Connection);
string lFile = null;
string holdfile = null;
short x = 0;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
string lDir = null;
// ERROR: Not supported in C#: OnErrorStatement
Cursor = System.Windows.Forms.Cursors.WaitCursor;
//lPath = upgradePath
System.Windows.Forms.Application.DoEvents();
lDir = Strings.LCase("\\\\" + lServerPath + "\\C\\4posServer\\");
cn = modRecordSet.openConnectionInstance(ref lPath);
if (cn == null) {
} else {
cat.let_ActiveConnection(cn);
foreach ( tbl in cat.Tables) {
if (tbl.Type == "LINK") {
System.Windows.Forms.Application.DoEvents();
//lFile = tbl.Name
if (tbl.Properties("Jet OLEDB:Link Datasource").Value != lDir + "pricing.mdb") {
tbl.Properties("Jet OLEDB:Link Datasource").Value = Strings.Replace(Strings.LCase(tbl.Properties("Jet OLEDB:Link Datasource").Value), Strings.LCase("C:\\4posServer\\"), lDir);
}
//DoEvents
//If tbl.Properties("Jet OLEDB:Link Datasource") <> lDIR & "pricing.mdb" Then
// tbl.Properties("Jet OLEDB:Link Datasource") = Replace(LCase(tbl.Properties("Jet OLEDB:Link Datasource")), LCase("C:\4posServer\"), lDIR)
//End If
}
}
//UPGRADE_NOTE: Object cat may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
cat = null;
cn.Close();
//UPGRADE_NOTE: Object cn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
cn = null;
cat = new ADOX.Catalog();
}
System.Windows.Forms.Application.DoEvents();
Cursor = System.Windows.Forms.Cursors.Default;
functionReturnValue = true;
return functionReturnValue;
buildPath_Error:
Cursor = System.Windows.Forms.Cursors.Default;
Interaction.MsgBox(Err().Description);
functionReturnValue = false;
return functionReturnValue;
}
public void Automaticload()
{
ADODB.Recordset rs = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
modReport.cnnDBreport.Close();
//modUpdate = 2
//updateStockMovement 'Defining the period from report are being loaded from
//I need code
if (fso.FileExists(modRecordSet.serverPath + "report" + this.lblUser.Tag + ".mdb"))
fso.DeleteFile(modRecordSet.serverPath + "report" + this.lblUser.Tag + ".mdb");
fso.CopyFile(modRecordSet.serverPath + "templateReport.mdb", modRecordSet.serverPath + "report" + this.lblUser.Tag + ".mdb", true);
modReport.openConnectionReport();
rs = modReport.getRSreport(ref "SELECT * FROM Report");
if (rs.RecordCount) {
modReport.cnnDBreport.Execute("UPDATE Report SET Report.Report_DayEndStartID = " + gDayEndStart + ", Report.Report_DayEndEndID = " + gDayEndEnd + ", Report.Report_Heading = '" + this.lblDayEnd.Text + "';");
} else {
modReport.cnnDBreport.Execute("INSERT INTO Report ( Report_DayEndStartID, Report_DayEndEndID, Report_Heading, ReportID ) SELECT " + gDayEndStart + ", " + gDayEndEnd + ", '" + this.lblDayEnd.Text + "', 1;");
}
frmUpdateDayEnd form = null;
form.Show();
lblDayEndCurrent.Text = lblDayEnd.Text;
}
private void cmdExportNow_Click(System.Object eventSender, System.EventArgs eventArgs)
{
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
//Set ex = New DatabaseExport
if (string.IsNullOrEmpty(Text1.Text)) {
Interaction.MsgBox("Please Enter file destination", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, _4PosBackOffice.NET.My.MyProject.Application.Info.Title);
return;
} else {
if (fso.FileExists(Text1.Text) == true) {
if (Interaction.MsgBox("File Exists, Do you wan to overwrite it?", MsgBoxStyle.ApplicationModal + MsgBoxStyle.Question + MsgBoxStyle.YesNo, _4PosBackOffice.NET.My.MyProject.Application.Info.Title) == MsgBoxResult.Yes) {
ex.FilePath = Text1.Text;
if (isClean() == false)
ex.ExportToCSV();
} else {
return;
}
this.Close();
} else {
if (isClean() == false)
ex.ExportToCSV();
this.Close();
}
}
}
public void saveDatabase(ref string lDatabase)
{
ADODB.Recordset rs = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
string lPath = null;
// ERROR: Not supported in C#: OnErrorStatement
rs = modRecordSet.getRS(ref "SELECT Company.Company_BackupPath, Right([Company_DayEndID],1) AS file FROM Company;");
//UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
if (Information.IsDBNull(rs.Fields("Company_BackupPath").Value)) {
goto saveDatabase_Error;
} else {
if (string.IsNullOrEmpty(rs.Fields("Company_BackupPath").Value)) {
lPath = _4PosBackOffice.NET.My.MyProject.Application.Info.DirectoryPath;
} else {
lPath = rs.Fields("Company_BackupPath").Value;
}
if (Strings.Right(lPath, 1) != "\\")
lPath = lPath + "\\";
lPath = lPath + rs.Fields("file").Value + ".bak";
if (fso.FileExists(lPath))
fso.DeleteFile(lPath, true);
fso.CopyFile(modRecordSet.serverPath + "" + lDatabase + ".mdb", lPath, true);
}
return;
saveDatabase_Error:
}
private Dictionary<string,long> GetSizesOfItems(IEnumerable<string> filesToMove)
{
var sizes=new Dictionary<string,long>(filesToMove.Count());
foreach (var path in filesToMove)
{
if (File.Exists(path))
{
sizes.Add(path, new FileInfo(path).Length);
}
else if (Directory.Exists(path))
{
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
Scripting.Folder folder = fso.GetFolder(path);
sizes.Add(path, (long)folder.Size);
}
}
return sizes;
}
private void cmdDelete_Click(System.Object eventSender, System.EventArgs eventArgs)
{
string lPath = null;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
System.Windows.Forms.ListViewItem lvItem = null;
lPath = modRecordSet.serverPath + "quote/";
foreach (ListViewItem lvItem_loopVariable in this.lvData.Items) {
lvItem = lvItem_loopVariable;
if (lvItem.Checked) {
if (fso.FileExists(lPath + Strings.Mid(lvItem.Name, 2))) {
fso.DeleteFile(lPath + Strings.Mid(lvItem.Name, 2));
}
modRecordSet.cnnDB.Execute("DELETE Quote.* From Quote WHERE (((Quote.Quote_Document)='" + Strings.Mid(lvItem.Name, 2) + "'));");
}
}
getData();
}
public void DeleteBlankID()
{
ADODB.Recordset rj = default(ADODB.Recordset);
ADODB.Recordset rs = default(ADODB.Recordset);
//UPGRADE_NOTE: Val was upgraded to Val_Renamed. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="A9E4979A-37FA-4718-9994-97DD76ED70A7"'
//Dim Val_Renamed As Object
Scripting.FileSystemObject oFSO = new Scripting.FileSystemObject();
//cnnDB.Execute "UPDATE " & st_Name & " SET HandHeldID = 0 WHERE Handheld_Barcode = " & vbNullString
rj = modRecordSet.getRS(ref "SELECT * FROM " + st_Name + " WHERE HandHeldID = 0");
short i = 0;
short j = 0;
if (rj.RecordCount > 0) {
Interaction.MsgBox("CSV file has missing Barcodes, please verify them.", MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, _4PosBackOffice.NET.My.MyProject.Application.Info.Title);
//fill test array
SaveArrayAsCSVFile.dChrom = new string[rj.RecordCount, 2];
for (i = 0; i <= rj.RecordCount - 1; i++) {
for (j = 0; j <= 1; j++) {
if (j == 0) {
SaveArrayAsCSVFile.dChrom[i, j] = rj.Fields("Handheld_Barcode").Value;
} else {
SaveArrayAsCSVFile.dChrom[i, j] = rj.Fields("Quantity").Value;
}
}
rj.moveNext();
}
if (oFSO.FolderExists("C:\\temp\\")) {
} else {
oFSO.CreateFolder("C:\\temp\\");
}
SaveArrayAsCSVFile.SaveAsCSV(SaveArrayAsCSVFile.dChrom, "C:\\temp\\" + st_Name + "_Missing.csv");
//App.Path & "\temp\" & st_Name & "_Missing.csv"
//Set rs = getRS("SELECT Catalogue_StockItemID FROM Catalogue WHERE Catalogue_Barcode = '" & rj("Handheld_Barcode") & "'")
//If rs.RecordCount > 0 Then cnnDB.Execute "UPDATE " & st_Name & " SET HandHeldID= " & rs("Catalogue_StockItemID") & " WHERE Handheld_Barcode = '" & rj("Handheld_Barcode") & "'"
modRecordSet.cnnDB.Execute("DELETE from " + st_Name + " WHERE HandHeldID = 0");
Interaction.MsgBox("CSV file has been as C:\\temp\\" + st_Name + "_Missing.csv");
}
//MsgBox "File was extracted and exported succesfully", vbApplicationModal + vbInformation + vbOKOnly, App.title
}
public void cmdLoad_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
// ERROR: Not supported in C#: OnErrorStatement
if (modReport.cnnDBreport.State)
modReport.cnnDBreport.Close();
modApplication.modUpdate = 0;
modApplication.updateStockMovement();
//Defining the period from report are being loaded from
//I need code
if (fso.FileExists(modRecordSet.serverPath + "report" + this.lblUser.Tag + ".mdb"))
fso.DeleteFile(modRecordSet.serverPath + "report" + this.lblUser.Tag + ".mdb");
fso.CopyFile(modRecordSet.serverPath + "templateReport.mdb", modRecordSet.serverPath + "report" + this.lblUser.Tag + ".mdb", true);
modReport.openConnectionReport();
rs = modReport.getRSreport(ref "SELECT * FROM Report");
if (rs.RecordCount) {
modReport.cnnDBreport.Execute("UPDATE Report SET Report.Report_DayEndStartID = " + gDayEndStart + ", Report.Report_DayEndEndID = " + gDayEndEnd + ", Report.Report_Heading = '" + this.lblDayEnd.Text + "';");
} else {
modReport.cnnDBreport.Execute("INSERT INTO Report ( Report_DayEndStartID, Report_DayEndEndID, Report_Heading, ReportID ) SELECT " + gDayEndStart + ", " + gDayEndEnd + ", '" + this.lblDayEnd.Text + "', 1;");
}
//Dim form As frmUpdateDayEnd
My.MyProject.Forms.frmUpdateDayEnd.Show();
lblDayEndCurrent.Text = lblDayEnd.Text;
return;
errLoad:
if (Err().Number == 70) {
//MsgBox Err.Number & " " & Err.Description & " " & Err.source & vbCrLf & vbCrLf & " Cannot perform this action, report database is being used by another person or program. Please close any program that might be using the report database and try again."
Interaction.MsgBox("You are logged into the 4POSBackOffice on another computer with the same Username & Password. You can not load reports if logged onto more than 1 PC. Please close the other 4POSOffice program first before attempting to 'Load Reports' on this PC");
//You have logged on to the 4POS Back Office program on another PC. You can not load reports if logged onto more than 1 PC. Please log out on the other PC/s.
} else {
Interaction.MsgBox(Err().Number + Err().Description);
}
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
short x = 0;
string strDBPath = null;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
if (!string.IsNullOrEmpty(DataList1.BoundText)) {
strDBPath = modRecordSet.serverPath + "month" + gMonth - this.cmbMonth.SelectedIndex + ".mdb";
if (fso.FileExists(strDBPath)) {
var _with1 = cnnDBStatement;
_with1.Provider = "MSDataShape";
cnnDBStatement.Open("Data Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strDBPath + ";User Id=liquid;Password=lqd;Jet OLEDB:System Database=" + modRecordSet.serverPath + "Secured.mdw");
SupplierStatement(ref Convert.ToInt32(DataList1.BoundText));
cnnDBStatement.Close();
} else {
Interaction.MsgBox("There is no previous month statement for this Supplier", MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly + MsgBoxStyle.Information, _4PosBackOffice.NET.My.MyProject.Application.Info.Title);
this.Close();
}
}
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
short x = 0;
string strDBPath = null;
strDBPath = modRecordSet.serverPath + "month" + gMonth + ".mdb";
//If FSO.FileExists(strDBPath) Then
// With cnnDBStatement
// .Provider = "MSDataShape"
// cnnDBStatement.Open "Data Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & ";User Id=liquid;Password=lqd;Jet OLEDB:System Database=" & serverPath & "Secured.mdw"
// End With
//
// For x = 0 To lstCustomer.ListCount - 1
// If lstCustomer.Selected(x) Then
// CustomerStatement lstCustomer.ItemData(x)
// End If
// Next x
// cnnDBStatement.Close
// Else
var _with1 = cnnDBStatement;
_with1.Provider = "MSDataShape";
cnnDBStatement.Open("Data Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + modRecordSet.serverPath + "pricing.mdb" + ";User Id=liquid;Password=lqd;Jet OLEDB:System Database=" + modRecordSet.serverPath + "Secured.mdw");
for (x = 0; x <= lstCustomer.Items.Count - 1; x++) {
if (lstCustomer.GetItemChecked(x)) {
CustomerStatement(ref Convert.ToInt32(lstCustomer.SelectedIndex));
}
}
cnnDBStatement.Close();
//MsgBox "Database [month" & gMonth & ".mdb] could not be found", vbApplicationModal + vbInformation + vbOKOnly, App.title
//Exit Sub
// End If
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
string strDBPath = null;
if (!string.IsNullOrEmpty(DataList1.BoundText)) {
if (this.cmbMonthEnd.SelectedIndex == 0){Interaction.MsgBox("Please select Month from List and then click PRINT.", MsgBoxStyle.Exclamation);return;
}
strDBPath = modRecordSet.serverPath + "month" + gMonth - this.cmbMonthEnd.SelectedIndex + ".mdb";
if (fso.FileExists(strDBPath)) {
var _with1 = cnnDBStatement;
_with1.Provider = "MSDataShape";
cnnDBStatement.Open("Data Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strDBPath + ";User Id=liquid;Password=lqd;Jet OLEDB:System Database=" + modRecordSet.serverPath + "Secured.mdw");
CustomerStatement(ref Convert.ToInt32(DataList1.BoundText));
cnnDBStatement.Close();
} else {
Interaction.MsgBox("There is no previous month statement for this Customer", MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly + MsgBoxStyle.Information, _4PosBackOffice.NET.My.MyProject.Application.Info.Title);
this.Close();
}
//strDBPath = serverPath & "month" & gMonth - Me.cmbMonth.ListIndex & ".mdb"
//If fso.FileExists(strDBPath) Then
// With cnnDBStatement
// .Provider = "MSDataShape"
// cnnDBStatement.Open "Data Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & ";User Id=liquid;Password=lqd;Jet OLEDB:System Database=" & serverPath & "Secured.mdw"
//
// End With
// CustomerStatement DataList1.BoundText
// cnnDBStatement.Close
//Else
// MsgBox "There is no previous month statement for this Customer", vbApplicationModal + vbOKOnly + vbInformation, App.title
// Unload Me
//End If
}
}
private void cmdRegistration_Click_OLD()
{
string lCode = null;
string lPassword = null;
int x = 0;
string lNewString = null;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
const string securtyStringReply = "9487203516";
//Exit Sub
if (Interaction.MsgBox("By Clicking 'Yes' you confirm that your company name is correct and you understand the licensing agreement." + Constants.vbCrLf + Constants.vbCrLf + "Do you wish to continue?", MsgBoxStyle.Question + MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2, "REGISTRATION") == MsgBoxResult.Yes) {
if (Strings.Len(gSecKey) == 12) {
lNewString = "";
for (x = 1; x <= Strings.Len(txtKey.Text); x++) {
if (Information.IsNumeric(Strings.Mid(txtKey.Text, x, 1))) {
lNewString = lNewString + Strings.InStr(securtyStringReply, Strings.Mid(txtKey.Text, x, 1)) - 1;
}
}
cmdCompany_Click(cmdCompany, new System.EventArgs());
return;
} else {
lNewString = "";
for (x = 1; x <= Strings.Len(txtKey.Text); x++) {
if (Information.IsNumeric(Strings.Mid(txtKey.Text, x, 1))) {
lNewString = lNewString + Strings.InStr(securtyStringReply, Strings.Mid(txtKey.Text, x, 1)) - 1;
}
}
if (!string.IsNullOrEmpty(lNewString)) {
if (System.Math.Abs(Convert.ToDouble(lNewString)) == System.Math.Abs(Convert.ToDouble(getSerialNumber()))) {
lPassword = "******";
lCode = getSerialNumber();
if (!string.IsNullOrEmpty(lCode)) {
lCode = Encrypt(lCode, lPassword);
cmdCompany_Click(cmdCompany, new System.EventArgs());
modRecordSet.cnnDB.Execute("UPDATE Company SET Company.Company_Code = '" + Strings.Replace(lCode, "'", "''") + "';");
frmRegister.Visible = false;
}
return;
}
}
}
}
Interaction.MsgBox("The 'Activation key' is invalid!", MsgBoxStyle.Exclamation, "4POS REGISTRATION");
}
private static void DeletePathWithLongFileNames(string path)
{
var tmpPath = @"\\?\" + path;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
fso.DeleteFolder(tmpPath, true);
}
private void cmdBuild_Click(System.Object eventSender, System.EventArgs eventArgs)
{
int x = 0;
string TMPgMasterPath = null;
ADODB.Recordset rs = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
string lDir = null;
// ERROR: Not supported in C#: OnErrorStatement
if (Interaction.MsgBox("A data instruction will prepare a download for each store of the latest stock and pricing data." + Constants.vbCrLf + Constants.vbCrLf + "Are you sure you wish to continue?", MsgBoxStyle.Question + MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2, "Prepare Download") == MsgBoxResult.Yes) {
TMPgMasterPath = gMasterPath;
gMasterPath = "c:\\4POSServer\\";
if (fso.FolderExists(gMasterPath + "Data\\")) {
} else {
fso.CreateFolder(gMasterPath + "Data\\");
}
lDir = FileSystem.Dir(gMasterPath + "data\\*.*");
while (!(string.IsNullOrEmpty(lDir))) {
fso.DeleteFile(gMasterPath + "data\\" + lDir, true);
lDir = FileSystem.Dir();
}
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
System.Windows.Forms.Application.DoEvents();
rs = getRSMaster(ref "SELECT 1 as MasterID, #" + DateAndTime.Today + "# as Master_DateReplica");
rs.save(gMasterPath + "Data\\Master.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM Catalogue");
rs.save(gMasterPath + "Data\\catalogue.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM PriceSet");
rs.save(gMasterPath + "Data\\PriceSet.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM Channel ORDER BY ChannelID");
rs.save(gMasterPath + "Data\\channel.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM Deposit ORDER BY DepositID");
rs.save(gMasterPath + "Data\\Deposit.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM PackSize ORDER BY PackSizeID");
rs.save(gMasterPath + "Data\\PackSize.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM Person ORDER BY PersonID");
rs.save(gMasterPath + "Data\\Person.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM PersonChannelLnk");
rs.save(gMasterPath + "Data\\PersonChannelLnk.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM PriceChannelLnk");
rs.save(gMasterPath + "Data\\PriceChannelLnk.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM PricingGroup ORDER BY PricingGroupID");
rs.save(gMasterPath + "Data\\PricingGroup.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM PricingGroupChannelLnk");
rs.save(gMasterPath + "Data\\PricingGroupChannelLnk.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM PropChannelLnk");
rs.save(gMasterPath + "Data\\PropChannelLnk.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM [Set] ORDER BY SetID");
rs.save(gMasterPath + "Data\\Set.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM SetItem");
rs.save(gMasterPath + "Data\\SetItem.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM Shrink ORDER BY ShrinkID");
rs.save(gMasterPath + "Data\\Shrink.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM ShrinkItem");
rs.save(gMasterPath + "Data\\ShrinkItem.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM StockGroup ORDER BY StockGroupID");
rs.save(gMasterPath + "Data\\StockGroup.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM StockItem ORDER BY StockItemID");
rs.save(gMasterPath + "Data\\stockitem.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM Supplier ORDER BY SupplierID");
rs.save(gMasterPath + "Data\\Supplier.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM SupplierDepositLnk");
rs.save(gMasterPath + "Data\\SupplierDepositLnk.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM Promotion");
rs.save(gMasterPath + "Data\\Promotion.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM PromotionItem");
rs.save(gMasterPath + "Data\\PromotionItem.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM StockBreak");
rs.save(gMasterPath + "Data\\StockBreak.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM RecipeStockItemLnk");
rs.save(gMasterPath + "Data\\RecipeStockItemLnk.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM CashTransaction");
rs.save(gMasterPath + "Data\\CashTransaction.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM Increment");
rs.save(gMasterPath + "Data\\Increment.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM IncrementStockItemLnk");
rs.save(gMasterPath + "Data\\IncrementStockItemLnk.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM IncrementQuantity");
rs.save(gMasterPath + "Data\\IncrementQuantity.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM Message");
rs.save(gMasterPath + "Data\\Message.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM MessageItem");
rs.save(gMasterPath + "Data\\MessageItem.rs", ADODB.PersistFormatEnum.adPersistADTG);
rs = modRecordSet.getRS(ref "SELECT * FROM StockItemMessageLnk");
rs.save(gMasterPath + "Data\\StockItemMessageLnk.rs", ADODB.PersistFormatEnum.adPersistADTG);
openConnectionWaitron();
rs = getRSwaitron(ref "SELECT * FROM POSMenu");
rs.save(gMasterPath + "Data\\POSMenu.rs", ADODB.PersistFormatEnum.adPersistADTG);
ExecCmd(ref gMasterPath + "wzzip.exe " + gMasterPath + "Data\\data.zip " + gMasterPath + "Data\\*.*");
if (fso.FileExists(gMasterPath + "Data.zip"))
fso.DeleteFile(gMasterPath + "Data.zip", true);
fso.CopyFile(gMasterPath + "Data\\data.zip", gMasterPath + "Data.zip", true);
rs = getRSMaster(ref "SELECT locationCompany_1.locationCompanyID, locationCompany.locationCompany_Email FROM locationCompany INNER JOIN (locationCompany AS locationCompany_1 INNER JOIN location ON locationCompany_1.locationCompany_LocationID = location.locationID) ON locationCompany.locationCompany_LocationID = location.locationID WHERE (((locationCompany_1.locationCompanyID)=" + lblCompany.Tag + "));");
// ERROR: Not supported in C#: OnErrorStatement
//MAPISession1.SignOn()
//MAPIMessages1.SessionID = MAPISession1.SessionID
//MAPIMessages1.Compose()
x = -1;
// ERROR: Not supported in C#: OnErrorStatement
while (!(rs.EOF)) {
if (!string.IsNullOrEmpty(rs.Fields("locationCompany_Email").Value)) {
x = x + 1;
//MAPIMessages1.RecipIndex = x
//MAPIMessages1.RecipDisplayName = rs.Fields("locationCompany_Email").Value
//MAPIMessages1.ResolveName()
}
rs.moveNext();
}
rs = getRSMaster(ref "SELECT locationAudit.locationAuditID, locationAudit.locationAudit_Email FROM locationCompany INNER JOIN locationAudit ON locationCompany.locationCompany_LocationID = locationAudit.locationAudit_LocationID WHERE (((locationCompany.locationCompanyID)=" + lblCompany.Tag + "));");
while (!(rs.EOF)) {
x = x + 1;
//MAPIMessages1.RecipIndex = x
//MAPIMessages1.RecipDisplayName = rs.Fields("locationAudit_Email").Value
//MAPIMessages1.ResolveName()
rs.moveNext();
}
//MAPIMessages1.MsgSubject = "4POS Data"
//MAPIMessages1.MsgNoteText = "4POS Pricing update as at " & Format(Now, "ddd, dd-mmm-yyyy hh:nn")
//MAPIMessages1.AttachmentType = MSMAPI.AttachTypeConstants.mapData
//MAPIMessages1.AttachmentName = "data.zip"
//MAPIMessages1.AttachmentPathName = gMasterPath & "data.zip"
//MAPIMessages1.Send()
//MAPISession1.SignOff()
gMasterPath = TMPgMasterPath;
}
this.Cursor = System.Windows.Forms.Cursors.Default;
return;
buildError:
if (Err().Number == 53) {
Interaction.MsgBox("Please ensure if you have 'WinZip Command Line Support Add-On' installed on your system.");
} else {
Interaction.MsgBox(Err().Number + " " + Err().Description);
}
}
public FrmInicio()
{
InitializeComponent();
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("es-ES");
System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("es");
string AuxDisco = "";
//##### Captura información del disco duro de la aplicación #####
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
Scripting.Drive selected_drive = fso.GetDrive(Application.ExecutablePath.Substring(0, 3));
AuxMsj = LblError.Text;
//##### SE CREA UN DIRECTORIO EN C: PARA GUARDAR LOS XML DE LOS REPORTES #####
if (!Directory.Exists("C:\\EntSolution"))
{
try
{
Directory.CreateDirectory("C:\\EntSolution");
}
catch (Exception)
{
MessageBox.Show("No se pudo crear la Carpeta C:\\EntSolution sin esta carpeta el sistema no podrá generar reportes... Contacte con su administrador de sistemas...", "Información del Sistema", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//##### SE CREA UN ARCHIVO PARA GUARDAR LOS LOGS DEL SISTEMA #####
if (!File.Exists("C:\\EntSolution\\Logs.txt"))
{
try
{
File.Create("C:\\EntSolution\\Logs.txt");
}
catch (Exception)
{
MessageBox.Show("No se pudo crear el archivo C:\\EntSolution\\Logs.txt sin este archivo el sistema no podrá generar logs de errores... Contacte a su administrador de sistemas...", "Información del Sistema", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//##### SE VALIDA SI LA SECCION ESTA PROTEGIDA EN CASO CONTRARIO SE LLAMA AL CONFIGURADOR #####
ClassModConfig Config = new ClassModConfig();
VarConst.NomSkin = Config.Skins;
if (Config.Proteccion("connectionStrings") == false)
{
FrmConfigCon FrmConfigCon = new FrmConfigCon();
FrmConfigCon.Text = FrmConfigCon.Text + "Configuración de Conexion";
FrmConfigCon.OptionConfig(1);
FrmConfigCon.ShowDialog();
MessageBox.Show(VarConst.BanError.ToString());
if (VarConst.BanError == true)
{
VarConst.BanError = false;
Environment.Exit(0);
}
else
{
Application.Exit();
}
return;
}
//##### SE VALIDA LA LICENCIA DEL SISTEMA #####
try
{
if (Config.Estado == "***")
{
if (selected_drive.IsReady)
{
if (selected_drive.SerialNumber.ToString().Length <= 15)
{
AuxDisco = Cifrar.Encriptar(selected_drive.SerialNumber.ToString());
}
else
{
string a = selected_drive.SerialNumber.ToString();
AuxDisco = a.Substring(0, 3) + "123" + a.Substring(a.Length - 3, 3);
AuxDisco = Cifrar.Encriptar(AuxDisco);
}
AuxDisco = AuxDisco.Substring(0, AuxDisco.Length - 2);
FrmConfigCon FrmConfigCon = new FrmConfigCon();
FrmConfigCon.Text = "Configuración de Licenciamiento";
FrmConfigCon.TxtBloqueo.Text = AuxDisco;
FrmConfigCon.OptionConfig(2);
FrmConfigCon.ShowDialog();
if (VarConst.BanError == true)
{
VarConst.BanError = false;
Environment.Exit(0);
}
else
{
Application.Exit();
}
}
}
else
{
if (Cifrar.Desencriptar(Config.Estado) == selected_drive.SerialNumber.ToString())
{
string AuxLic = Cifrar.Desencriptar(Config.Licencia);
DataTable tbl = new DataTable();
tbl.Clear();
if (conexion.consulta("SELECT EMP_NOMBRE FROM TBLEMPRESA WHERE EMP_BANNOM = 'PRI' AND EMP_RIF LIKE '" + AuxLic.Split('#')[2].Substring(3, 1) + "-%" + AuxLic.Split('#')[2].Substring(4, 3) + "__'"))
{
tbl = conexion.datos();
if (tbl.Rows.Count > 0)
{
if (AuxLic.Split('#')[2].Substring(0, 3) != "PER")
{
if (Convert.ToDateTime(AuxLic.Split('#')[1]).AddDays(-30) < DateTime.Now)
{
//MidPrincipal.TxtMantenimiento.Visibility = DevExpress.XtraBars.BarItemVisibility.Always;
//MidPrincipal.TxtMantenimiento.Caption = "Lic. de Mantenimiento: " + DateDiff(DateInterval.Day, Now, CDate(AuxLic.Split("#")(1))).ToString + " Dias"
}
//##### SE VALIDA ACTUALIZACION #####
DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
DateTime now = DateTime.Now;
Calendar cal = dfi.Calendar;
try
{
bool BanU = false;
switch (Config.Update.Substring(0, 1))
{
case "D":
BanU = true;
break;
case "S":
decimal Semana = cal.GetWeekOfYear(now, dfi.CalendarWeekRule, dfi.FirstDayOfWeek);
if (Convert.ToInt16(Config.Update.Split('#')[0].Split('-')[1]) <= Semana)
{
BanU = true;
if (Semana >= 52)
{
Config.Update = Config.Update.Split('#')[0].Split('-')[0] + "-" + "1" + "#" + Config.Update.Split('#')[1] + "#" + Config.Update.Split('#')[2];
}
else
{
Config.Update = Config.Update.Split('#')[0].Split('-')[0] + "-" + (now.Month + 1).ToString() + "#" + Config.Update.Split('#')[1] + "#" + Config.Update.Split('#')[2];
}
}
break;
case "M":
if (Convert.ToInt16(Config.Update.Split('#')[0].Split('-')[1]) <= now.Month)
{
BanU = true;
if (now.Month >= 12)
{
Config.Update = Config.Update.Split('#')[0].Split('-')[0] + "-" + "1" + "#" + Config.Update.Split('#')[1] + "#" + Config.Update.Split('#')[2];
}
else
{
Config.Update = Config.Update.Split('#')[0].Split('-')[0] + "-" + (now.Month + 1).ToString() + "#" + Config.Update.Split('#')[1] + "#" + Config.Update.Split('#')[2];
}
}
break;
}
if (BanU == true)
{
Config.ModificarSet("", "", "", "", Config.Update);
//FrmActualizador.ShowDialog();
}
}
catch (Exception ex)
{
MsjErr.MsjBox(5, "ERR010-01", ex.ToString());
}
}
else
{
VarConst.BanAct = false;
}
}
else
{
MsjErr.MsjBox(1, "ERR002-01", "La Licencia instalada en este equipo no es valida");
MessageBox.Show("La Licencia instalada en este equipo no es valida");
Environment.Exit(0);
}
}
}
else
{
MsjErr.MsjBox(1, "ERR002-01", "La Licencia instalada en este equipo no es valida");
Environment.Exit(0);
}
}
}
catch (Exception ex)
{
MsjErr.MsjBox(1, "ERR002-03", ex.ToString());
Environment.Exit(0);
}
try
{
VarConst.NumEmp = Cifrar.Desencriptar(Config.InfEmp + "==").Split('#')[1];
VarConst.NumTra = Cifrar.Desencriptar(Config.InfEmp + "==").Split('#')[2];
}
catch (Exception ex)
{
MsjErr.MsjBox(1, "ERR007-01", ex.ToString());
Environment.Exit(0);
}
Config.Destruc();
if (LicEmp())
{
conexion.CargarComboBox("SELECT EMP_NOMBRE FROM TBLEMPRESA WHERE EMP_REG = '1' ORDER BY EMP_ID LIMIT " + VarConst.NumEmp + "", CmbEmpresa, "EMP_NOMBRE");
CmbEmpresa.SelectedIndex = 0;
}
else
{
MsjErr.MsjBox(1, "ERR007-02", "Hay mas empresas o usuarios registrados que los que admite la licencia");
Environment.Exit(0);
}
}
private void cmdScale_Click(System.Object eventSender, System.EventArgs eventArgs)
{
string mySQL = null;
ADODB.Recordset rsScale = default(ADODB.Recordset);
ADODB.Recordset rsData = default(ADODB.Recordset);
string lString = "";
string lFormat = null;
string HeadingString1 = null;
string HeadingString2 = null;
string lWeighted = null;
Scripting.FileSystemObject FSO = new Scripting.FileSystemObject();
Scripting.TextStream lTextstreamC = default(Scripting.TextStream);
// ERROR: Not supported in C#: OnErrorStatement
rsScale = modRecordSet.getRS(ref "SELECT Scale.* FROM Scale;");
while (!(rsScale.EOF)) {
if (My.MyProject.Forms.frmUpdatePOScriteria.frmType[0].Visible == true) {
mySQL = "SELECT Right('0000'+[Catalogue_Barcode],4) AS code, Format([CatalogueChannelLnk_Price],'Fixed') AS price, StockItem.StockItem_Name, StockGroup.StockGroup_Name, StockItem.StockItem_NonWeighted FROM POSUpdate_PriceChangeSummary INNER JOIN ((StockItem INNER JOIN (CatalogueChannelLnk INNER JOIN Catalogue ON (CatalogueChannelLnk.CatalogueChannelLnk_StockItemID = Catalogue.Catalogue_StockItemID) AND (CatalogueChannelLnk.CatalogueChannelLnk_Quantity = Catalogue.Catalogue_Quantity)) ON StockItem.StockItemID = Catalogue.Catalogue_StockItemID) INNER JOIN StockGroup ON StockItem.StockItem_StockGroupID = StockGroup.StockGroupID) ON POSUpdate_PriceChangeSummary.CatalogueChannelLnk_StockItemID = StockItem.StockItemID WHERE (((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1) AND ((Catalogue.Catalogue_Quantity)=1) AND ((StockItem.StockItem_Disabled)=False) AND ((StockItem.StockItem_VariablePrice)=True));";
} else {
mySQL = "SELECT Right('0000'+[Catalogue_Barcode],4) AS code, Format([CatalogueChannelLnk_Price],'Fixed') AS price, StockItem.StockItem_Name, StockGroup.StockGroup_Name, StockItem.StockItem_NonWeighted FROM (StockItem INNER JOIN (CatalogueChannelLnk INNER JOIN Catalogue ON (CatalogueChannelLnk.CatalogueChannelLnk_Quantity = Catalogue.Catalogue_Quantity) AND (CatalogueChannelLnk.CatalogueChannelLnk_StockItemID = Catalogue.Catalogue_StockItemID)) ON StockItem.StockItemID = Catalogue.Catalogue_StockItemID) INNER JOIN StockGroup ON StockItem.StockItem_StockGroupID = StockGroup.StockGroupID WHERE (((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1) AND ((Catalogue.Catalogue_Quantity)=1) AND ((StockItem.StockItem_Disabled)=False) AND ((StockItem.StockItem_VariablePrice)=True));";
}
rsData = modRecordSet.getRS(ref mySQL);
// Select Case rsScale("Scale_format")
// Case 0
// rsData.MoveFirst
// Do Until rsData.EOF
// HeadingString1 = Left(rsData("Stockitem_Name"), 16)
// HeadingString2 = ""
// If Len(rsData("Stockitem_Name")) > 16 Then
// Do Until Right(HeadingString1, 1) = " "
// HeadingString1 = Left(HeadingString1, Len(HeadingString1) - 1)
// Loop
// HeadingString2 = Mid(rsData("Stockitem_Name"), Len(HeadingString1) + 1)
// End If
// HeadingString1 = Left(HeadingString1 & String(16, " "), 16)
// HeadingString2 = Left(HeadingString2 & String(16, " "), 16)
// If rsData("StockItem_NonWeighted") Then lWeighted = "01" Else lWeighted = "00"
// lstring = lstring & vbCrLf & "#" & rsData("code") & "$" & rsData("price") & "%#000%$" & lWeighted & "%(A" & HeadingString1 & "%)A" & HeadingString2 & "%[" & Left(rsData("StockGroup_Name") & String(15, " "), 15)
// rsData.moveNext
// Loop
// Case 1
rsData.MoveFirst();
while (!(rsData.EOF)) {
HeadingString1 = Strings.Left(rsData.Fields("Stockitem_Name").Value, 16);
//16 was 22
HeadingString2 = "";
//16 was 22
if (Strings.Len(rsData.Fields("Stockitem_Name").Value) > 16) {
while (!(Strings.Right(HeadingString1, 1) == " " | string.IsNullOrEmpty(HeadingString1))) {
HeadingString1 = Strings.Left(HeadingString1, Strings.Len(HeadingString1) - 1);
}
HeadingString2 = Strings.Mid(rsData.Fields("Stockitem_Name").Value, Strings.Len(HeadingString1) + 1);
}
HeadingString1 = Strings.Left(HeadingString1 + new string(" ", 16), 16);
//16 was 22
HeadingString2 = Strings.Left(HeadingString2 + new string(" ", 22), 22);
//16 was 22
if (rsData.Fields("StockItem_NonWeighted").Value)
lWeighted = "01";
else
lWeighted = "00";
// lstring = lstring & vbCrLf & "%*0 #" & rsData("code") & " $" & Right("00000" & Replace(rsData("price"), ".", ""), 5) & " %#000 %$" & lWeighted & "%&" & rsScale("ScaleID") & rsData("code") & " %(A" & HeadingString1 & " %)A" & HeadingString2 & " %[" & Left(rsData("StockGroup_Name") & String(15, " "), 15) & " %]" & String(15, " ") & " %~"
//If lString = "" Then
// lString = "%*0 #" & rsData("code") & " $" & Right("00000" & FormatNumber(rsData("price"), 2), 6) & " %#000 %& %(A" & HeadingString1 & " %)A" & HeadingString2 & " %$" & lWeighted
//Else
lString = lString + Constants.vbCrLf + "%*0 #" + rsData.Fields("code").Value + " $" + Strings.Right("00000" + Strings.FormatNumber(rsData.Fields("price").Value, 2), 6) + " %#000 %& %(A" + HeadingString1 + " %)A" + HeadingString2 + " %$" + lWeighted;
//End If
rsData.moveNext();
}
// End Select
//FOR NEW SCALE ITEMS
if (My.MyProject.Forms.frmUpdatePOScriteria.frmType[0].Visible == true) {
//UPGRADE_WARNING: Couldn't resolve default property of object mySQL. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
mySQL = "SELECT Right('0000'+[Catalogue_Barcode],4) AS code, Format([CatalogueChannelLnk_Price],'Fixed') AS price, StockItem.StockItem_Name, StockGroup.StockGroup_Name, StockItem.StockItem_NonWeighted FROM POSUpdate_PriceNewSummary INNER JOIN ((StockItem INNER JOIN (CatalogueChannelLnk INNER JOIN Catalogue ON (CatalogueChannelLnk.CatalogueChannelLnk_StockItemID = Catalogue.Catalogue_StockItemID) AND (CatalogueChannelLnk.CatalogueChannelLnk_Quantity = Catalogue.Catalogue_Quantity)) ON StockItem.StockItemID = Catalogue.Catalogue_StockItemID) INNER JOIN StockGroup ON StockItem.StockItem_StockGroupID = StockGroup.StockGroupID) ON POSUpdate_PriceNewSummary.CatalogueChannelLnk_StockItemID = StockItem.StockItemID WHERE (((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1) AND ((Catalogue.Catalogue_Quantity)=1) AND ((StockItem.StockItem_Disabled)=False) AND ((StockItem.StockItem_VariablePrice)=True));";
rsData = modRecordSet.getRS(ref mySQL);
rsData.MoveFirst();
while (!(rsData.EOF)) {
HeadingString1 = Strings.Left(rsData.Fields("Stockitem_Name").Value, 16);
//16 was 22
HeadingString2 = "";
//16 was 22
if (Strings.Len(rsData.Fields("Stockitem_Name").Value) > 16) {
while (!(Strings.Right(HeadingString1, 1) == " " | string.IsNullOrEmpty(HeadingString1))) {
HeadingString1 = Strings.Left(HeadingString1, Strings.Len(HeadingString1) - 1);
}
HeadingString2 = Strings.Mid(rsData.Fields("Stockitem_Name").Value, Strings.Len(HeadingString1) + 1);
}
HeadingString1 = Strings.Left(HeadingString1 + new string(" ", 16), 16);
//16 was 22
HeadingString2 = Strings.Left(HeadingString2 + new string(" ", 22), 22);
//16 was 22
if (rsData.Fields("StockItem_NonWeighted").Value)
lWeighted = "01";
else
lWeighted = "00";
lString = lString + Constants.vbCrLf + "%*0 #" + rsData.Fields("code").Value + " $" + Strings.Right("00000" + Strings.FormatNumber(rsData.Fields("price").Value, 2), 6) + " %#000 %& %(A" + HeadingString1 + " %)A" + HeadingString2 + " %$" + lWeighted;
rsData.moveNext();
}
}
if (FSO.FileExists(rsScale.Fields("Scale_Path").Value))
FSO.DeleteFile(rsScale.Fields("Scale_Path").Value);
if (Strings.Len(lString)) {
lTextstreamC = FSO.CreateTextFile(rsScale.Fields("Scale_Path").Value, true);
Debug.Print(Strings.Trim(Strings.Left(lString, Strings.Len(lString) - 1)));
lTextstreamC.Write(Strings.Mid(lString, 3));
lTextstreamC.Close();
//Open rsScale("Scale_Path") For Output As #7
//Print #7, mID(lString, 3, Len(lString) - 5)
//Close #7
if (FSO.FileExists(modRecordSet.serverPath + "teraoka.bat"))
Interaction.Shell(modRecordSet.serverPath + "teraoka.bat", AppWinStyle.MinimizedNoFocus);
}
rsScale.moveNext();
}
modRecordSet.cnnDB.Execute("UPDATE Scale SET Scale.Scale_Update = False;");
}
public static void MoveFolder(string source, string destination)
{
//[参照の追加]でMicrosoft Scripting Runtimeを事前追加すること
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
fso.MoveFolder(source, destination);
}
private void doMonthEnd()
{
string sql = null;
string lPath = null;
ADODB.Recordset rs = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
ADODB.Connection dbcnnMonth = default(ADODB.Connection);
//fix Server Path in Month End DBs
string databaseName = null;
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
System.Windows.Forms.Application.DoEvents();
//BuildAll
rs = modRecordSet.getRS(ref "SELECT Company_MonthEndID, Company_OpenDebtor From Company");
lPath = modRecordSet.serverPath + "month" + rs.Fields("Company_MonthEndID").Value + ".mdb";
//fix Server Path in Month End DBs
databaseName = "month" + rs.Fields("Company_MonthEndID").Value + ".mdb";
sql = "UPDATE Company Set Company.Company_MonthEndID = Company.Company_MonthEndID + 1;";
modRecordSet.cnnDB.Execute(sql);
sql = "INSERT INTO MonthEnd ( MonthEndID, MonthEnd_Date, MonthEnd_Days, MonthEnd_BudgetSales, MonthEnd_BudgetPurchases ) SELECT Company.Company_MonthEndID, Now(), 20, 100000, 100000 FROM Company;";
modRecordSet.cnnDB.Execute(sql);
modRecordSet.cnnDB.Execute("UPDATE Company INNER JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID SET DayEnd.DayEnd_MonthEndID = [Company]![Company_MonthEndID];");
fso.CopyFile(modRecordSet.serverPath + "template.mdb", lPath, true);
dbcnnMonth = new ADODB.Connection();
var _with1 = dbcnnMonth;
_with1.Provider = "Microsoft.ACE.OLEDB.12.0";
_with1.Properties("Jet OLEDB:System Database").Value = "" + modRecordSet.serverPath + "Secured.mdw";
_with1.Open(lPath, "liquid", "lqd");
sql = "UPDATE StockitemHistory SET StockitemHistory.StockitemHistory_Month12 = [StockitemHistory]![StockitemHistory_Month11], StockitemHistory.StockitemHistory_Month11 = [StockitemHistory]![StockitemHistory_Month10], StockitemHistory.StockitemHistory_Month10 = [StockitemHistory]![StockitemHistory_Month9], StockitemHistory.StockitemHistory_Month9 = [StockitemHistory]![StockitemHistory_Month8], StockitemHistory.StockitemHistory_Month8 = [StockitemHistory]![StockitemHistory_Month7], StockitemHistory.StockitemHistory_Month7 = [StockitemHistory]![StockitemHistory_Month6], StockitemHistory.StockitemHistory_Month6 = [StockitemHistory]![StockitemHistory_Month5], StockitemHistory.StockitemHistory_Month5 = [StockitemHistory]![StockitemHistory_Month4], ";
sql = sql + "StockitemHistory.StockitemHistory_Month4 = [StockitemHistory]![StockitemHistory_Month3], StockitemHistory.StockitemHistory_Month3 = [StockitemHistory]![StockitemHistory_Month2], StockitemHistory.StockitemHistory_Month2 = [StockitemHistory]![StockitemHistory_Month1], StockitemHistory.StockitemHistory_Month1 = 0;";
modRecordSet.cnnDB.Execute(sql);
sql = "INSERT INTO Customer ( CustomerID, Customer_ChannelID, Customer_InvoiceName, Customer_DepartmentName, Customer_FirstName, Customer_Surname, Customer_PhysicalAddress, Customer_PostalAddress, Customer_Telephone, Customer_Fax, Customer_Email, Customer_Disabled, Customer_Terms, Customer_CreditLimit, Customer_Current, Customer_30Days, Customer_60Days, Customer_90Days, Customer_120Days, Customer_150Days, Customer_PrintStatement,Customer_VATNumber ) ";
sql = sql + "SELECT M_Customer.CustomerID, M_Customer.Customer_ChannelID, M_Customer.Customer_InvoiceName, M_Customer.Customer_DepartmentName, M_Customer.Customer_FirstName, M_Customer.Customer_Surname, M_Customer.Customer_PhysicalAddress, M_Customer.Customer_PostalAddress, M_Customer.Customer_Telephone, M_Customer.Customer_Fax, M_Customer.Customer_Email, M_Customer.Customer_Disabled, M_Customer.Customer_Terms, M_Customer.Customer_CreditLimit, M_Customer.Customer_Current, M_Customer.Customer_30Days, M_Customer.Customer_60Days, M_Customer.Customer_90Days, M_Customer.Customer_120Days, M_Customer.Customer_150Days, M_Customer.Customer_PrintStatement, M_Customer.Customer_VATNumber FROM M_Customer;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO CustomerTransaction ( CustomerTransactionID, CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done, CustomerTransaction_Main, CustomerTransaction_Child, CustomerTransaction_Allocated ) ";
sql = sql + "SELECT M_CustomerTransaction.CustomerTransactionID, M_CustomerTransaction.CustomerTransaction_CustomerID, M_CustomerTransaction.CustomerTransaction_TransactionTypeID, M_CustomerTransaction.CustomerTransaction_DayEndID, M_CustomerTransaction.CustomerTransaction_MonthEndID, M_CustomerTransaction.CustomerTransaction_ReferenceID, M_CustomerTransaction.CustomerTransaction_Date, M_CustomerTransaction.CustomerTransaction_Description, M_CustomerTransaction.CustomerTransaction_Amount, M_CustomerTransaction.CustomerTransaction_Reference, M_CustomerTransaction.CustomerTransaction_PersonName, M_CustomerTransaction.CustomerTransaction_Done, M_CustomerTransaction.CustomerTransaction_Main, M_CustomerTransaction.CustomerTransaction_Child, M_CustomerTransaction.CustomerTransaction_Allocated FROM M_CustomerTransaction;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO CustomerTransactionAlloc ( CustomerTransactionAllocID, CustomerTransactionAlloc_CustomerID, CustomerTransactionAlloc_MainID, CustomerTransactionAlloc_ChildID, CustomerTransactionAlloc_Date, CustomerTransactionAlloc_Description, CustomerTransactionAlloc_Amount, CustomerTransactionAlloc_Reference, CustomerTransactionAlloc_PersonName ) ";
sql = sql + "SELECT M_CustomerTransactionAlloc.CustomerTransactionAllocID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_CustomerID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_MainID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_ChildID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Date, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Description, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Amount, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Reference, M_CustomerTransactionAlloc.CustomerTransactionAlloc_PersonName FROM M_CustomerTransactionAlloc;";
dbcnnMonth.Execute(sql);
if (rs.Fields("Company_OpenDebtor").Value == true) {
//sql = "DELETE M_CustomerTransaction.* FROM M_CustomerTransaction WHERE (((M_CustomerTransaction.CustomerTransaction_Allocated)=[M_CustomerTransaction].[CustomerTransaction_Amount]) AND ((M_CustomerTransaction.CustomerTransaction_Allocated)<>0));"
//dbcnnMonth.Execute sql
sql = "DELETE M_CustomerTransaction.* FROM M_CustomerTransaction;";
dbcnnMonth.Execute(sql);
//version 1 problem
//sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done, CustomerTransaction_Main, CustomerTransaction_Child, CustomerTransaction_Allocated ) "
//sql = sql & "SELECT CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, (CustomerTransaction.CustomerTransaction_Amount-CustomerTransaction.CustomerTransaction_Allocated) AS SumOfCustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference & ' B/F' AS ref, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Done, CustomerTransaction.CustomerTransaction_Main, CustomerTransaction.CustomerTransaction_Child, CustomerTransaction.CustomerTransaction_Allocated "
//sql = sql & "From CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_Allocated)<>[CustomerTransaction].[CustomerTransaction_Amount])) AND (((CustomerTransaction.CustomerTransaction_Allocated)<>0));"
//dbcnnMonth.Execute sql
//version 2
//sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done ) "
//sql = sql & "SELECT CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, M_Company.Company_DayEndID, M_Company.Company_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, (CustomerTransaction.CustomerTransaction_Amount-CustomerTransaction.CustomerTransaction_Allocated) AS SumOfCustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference & '" & " *" & "' AS ref, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Done "
//sql = sql & "FROM CustomerTransaction, M_Company WHERE (((CustomerTransaction.CustomerTransaction_Allocated)<>[CustomerTransaction].[CustomerTransaction_Amount])) ORDER BY CustomerTransaction.CustomerTransactionID;"
//dbcnnMonth.Execute sql
//version 3
sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done ) ";
sql = sql + "SELECT CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, (CustomerTransaction.CustomerTransaction_Amount-CustomerTransaction.CustomerTransaction_Allocated) AS SumOfCustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference & '" + " *" + "' AS ref, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Done ";
sql = sql + "FROM CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_Allocated)<>[CustomerTransaction].[CustomerTransaction_Amount])) ORDER BY CustomerTransaction.CustomerTransactionID;";
dbcnnMonth.Execute(sql);
} else {
//DO THE OLD WAY
sql = "DELETE M_CustomerTransaction.* FROM M_CustomerTransaction;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName ) SELECT CustomerTransaction.CustomerTransaction_CustomerID, 7 AS transType, M_Company.Company_DayEndID, M_Company.Company_MonthEndID, 0 AS reference, Now() AS [date], '' AS [desc], Sum(CustomerTransaction.CustomerTransaction_Amount) AS SumOfCustomerTransaction_Amount, 'Month End' AS ref, 'System' AS person From CustomerTransaction, M_Company GROUP BY CustomerTransaction.CustomerTransaction_CustomerID, M_Company.Company_DayEndID, M_Company.Company_MonthEndID;";
dbcnnMonth.Execute(sql);
}
sql = "UPDATE M_Customer SET M_Customer.Customer_150Days = [M_Customer]![Customer_150Days]+[M_Customer]![Customer_120Days], M_Customer.Customer_120Days = [M_Customer]![Customer_90Days], M_Customer.Customer_90Days = [M_Customer]![Customer_60Days], M_Customer.Customer_60Days = [M_Customer]![Customer_30Days], M_Customer.Customer_30Days = [M_Customer]![Customer_Current], M_Customer.Customer_Current = 0;";
dbcnnMonth.Execute(sql);
//Debtor Age shifting if Credit
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_120Days = iif(([M_Customer]![Customer_150Days]<0),([M_Customer]![Customer_120Days]+[M_Customer]![Customer_150Days]),[M_Customer]![Customer_120Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_150Days = iif(([M_Customer]![Customer_150Days]<0),0,[M_Customer]![Customer_150Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_90Days = iif(([M_Customer]![Customer_120Days]<0),([M_Customer]![Customer_90Days]+[M_Customer]![Customer_120Days]),[M_Customer]![Customer_90Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_120Days = iif(([M_Customer]![Customer_120Days]<0),0,[M_Customer]![Customer_120Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_60Days = iif(([M_Customer]![Customer_90Days]<0),([M_Customer]![Customer_60Days]+[M_Customer]![Customer_90Days]),[M_Customer]![Customer_60Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_90Days = iif(([M_Customer]![Customer_90Days]<0),0,[M_Customer]![Customer_90Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_30Days = iif(([M_Customer]![Customer_60Days]<0),([M_Customer]![Customer_30Days]+[M_Customer]![Customer_60Days]),[M_Customer]![Customer_30Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_60Days = iif(([M_Customer]![Customer_60Days]<0),0,[M_Customer]![Customer_60Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_Current = iif(([M_Customer]![Customer_30Days]<0),([M_Customer]![Customer_Current]+[M_Customer]![Customer_30Days]),[M_Customer]![Customer_Current]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_30Days = iif(([M_Customer]![Customer_30Days]<0),0,[M_Customer]![Customer_30Days]);");
//Debtor Age shifting if Credit
//Tranfer change sql = "INSERT INTO DayEndStockItemLnk ( DayEndStockItemLnk_DayEndID, DayEndStockItemLnk_StockItemID, DayEndStockItemLnk_Quantity, DayEndStockItemLnk_QuantitySales, DayEndStockItemLnk_QuantityShrink, DayEndStockItemLnk_QuantityGRV, DayEndStockItemLnk_ListCost, DayEndStockItemLnk_ActualCost, DayEndStockItemLnk_Warehouse ) SELECT M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, M_DayEndStockItemLnk.DayEndStockItemLnk_StockItemID, M_DayEndStockItemLnk.DayEndStockItemLnk_Quantity, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityShrink, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV, M_DayEndStockItemLnk.DayEndStockItemLnk_ListCost, M_DayEndStockItemLnk.DayEndStockItemLnk_ActualCost, M_DayEndStockItemLnk.DayEndStockItemLnk_Warehouse From M_DayEndStockItemLnk, M_Company WHERE (((M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<>[M_Company]![Company_DayEndID]));"
sql = "INSERT INTO DayEndStockItemLnk ( DayEndStockItemLnk_DayEndID, DayEndStockItemLnk_StockItemID, DayEndStockItemLnk_Quantity, DayEndStockItemLnk_QuantitySales, DayEndStockItemLnk_QuantityShrink, DayEndStockItemLnk_QuantityGRV, DayEndStockItemLnk_QuantityTransafer, DayEndStockItemLnk_ListCost, DayEndStockItemLnk_ActualCost, DayEndStockItemLnk_Warehouse ) SELECT M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, M_DayEndStockItemLnk.DayEndStockItemLnk_StockItemID, M_DayEndStockItemLnk.DayEndStockItemLnk_Quantity, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityShrink, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityTransafer, M_DayEndStockItemLnk.DayEndStockItemLnk_ListCost, M_DayEndStockItemLnk.DayEndStockItemLnk_ActualCost, M_DayEndStockItemLnk.DayEndStockItemLnk_Warehouse ";
sql = sql + "From M_DayEndStockItemLnk, M_Company WHERE (((M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<>[M_Company]![Company_DayEndID]));";
dbcnnMonth.Execute(sql);
sql = "DELETE M_DayEndStockItemLnk.* From M_DayEndStockItemLnk, M_Company WHERE (((M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<>[M_Company]![Company_DayEndID]));";
dbcnnMonth.Execute(sql);
//Tranfer change
sql = "INSERT INTO StockTransferWH ( StockTransferWH_Date, StockTransferWH_DayEndID, StockTransferWH_PersonID, StockTransferWH_WHFrom, StockTransferWH_WHTo, StockTransferWH_StockItemID, StockTransferWH_Qty ) SELECT M_StockTransferWH.StockTransferWH_Date, M_StockTransferWH.StockTransferWH_DayEndID, M_StockTransferWH.StockTransferWH_PersonID, M_StockTransferWH.StockTransferWH_WHFrom, M_StockTransferWH.StockTransferWH_WHTo, M_StockTransferWH.StockTransferWH_StockItemID, M_StockTransferWH.StockTransferWH_Qty ";
sql = sql + "From M_StockTransferWH, M_Company WHERE (((M_StockTransferWH.StockTransferWH_DayEndID)<>[M_Company]![Company_DayEndID]));";
dbcnnMonth.Execute(sql);
sql = "DELETE M_StockTransferWH.* From M_StockTransferWH, M_Company WHERE (((M_StockTransferWH.StockTransferWH_DayEndID)<>[M_Company]![Company_DayEndID]));";
dbcnnMonth.Execute(sql);
//Tranfer change
sql = "INSERT INTO Declaration ( DeclarationID, Declaration_POSID, Declaration_DayEndID, Declaration_Date, Declaration_Cash, Declaration_CashServer, Declaration_CashCount, Declaration_Cheque, Declaration_ChequeServer, Declaration_ChequeCount, Declaration_Card, Declaration_CardServer, Declaration_CardCount, Declaration_Payout, Declaration_PayoutServer, Declaration_PayoutCount, Declaration_Total, Declaration_TotalServer, Declaration_TotalCount ) ";
sql = sql + "SELECT M_Declaration.DeclarationID, M_Declaration.Declaration_POSID, M_Declaration.Declaration_DayEndID, M_Declaration.Declaration_Date, M_Declaration.Declaration_Cash, M_Declaration.Declaration_CashServer, M_Declaration.Declaration_CashCount, M_Declaration.Declaration_Cheque, M_Declaration.Declaration_ChequeServer, M_Declaration.Declaration_ChequeCount, M_Declaration.Declaration_Card, M_Declaration.Declaration_CardServer, M_Declaration.Declaration_CardCount, M_Declaration.Declaration_Payout, M_Declaration.Declaration_PayoutServer, M_Declaration.Declaration_PayoutCount, M_Declaration.Declaration_Total, M_Declaration.Declaration_TotalServer, M_Declaration.Declaration_TotalCount FROM M_Declaration;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_Declaration.* FROM M_Declaration;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO Sale ( SaleID, Sale_PosID, Sale_DeclarationID, Sale_ChannelID, Sale_PersonID, Sale_ManagerID, Sale_DayEndID, Sale_Date, Sale_DatePOS, Sale_SubTotal, Sale_Discount, Sale_Total, Sale_Tender, Sale_Slip, Sale_PaymentType, Sale_Reference,Sale_CardRef,Sale_OrderRef,Sale_SerialRef,Sale_Cash,Sale_Card,Sale_Cheque,Sale_CDebit,Sale_PersonShiftID,Sale_TableNumber,Sale_GuestCount,Sale_SlipCount,Sale_Gratuity,Sale_DisChk,Sale_SaleChk ) ";
sql = sql + "SELECT M_Sale.SaleID, M_Sale.Sale_PosID, M_Sale.Sale_DeclarationID, M_Sale.Sale_ChannelID, M_Sale.Sale_PersonID, M_Sale.Sale_ManagerID, M_Sale.Sale_DayEndID, M_Sale.Sale_Date, M_Sale.Sale_DatePOS, M_Sale.Sale_SubTotal, M_Sale.Sale_Discount, M_Sale.Sale_Total, M_Sale.Sale_Tender, M_Sale.Sale_Slip, M_Sale.Sale_PaymentType, Sale_Reference,M_Sale.Sale_CardRef,M_Sale.Sale_OrderRef,M_Sale.Sale_SerialRef, M_Sale.Sale_Cash,M_Sale.Sale_Card,M_Sale.Sale_Cheque,M_Sale.Sale_CDebit,M_Sale.Sale_PersonShiftID,M_Sale.Sale_TableNumber,M_Sale.Sale_GuestCount,M_Sale.Sale_SlipCount,M_Sale.Sale_Gratuity,M_Sale.Sale_DisChk,M_Sale.Sale_SaleChk FROM M_Sale;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO SaleItem ( SaleItemID, SaleItem_SaleID, SaleItem_StockItemID, SaleItem_ShrinkQuantity, SaleItem_Quantity, SaleItem_LineNo, SaleItem_Vat, SaleItem_PriceOriginal, SaleItem_Price, SaleItem_Revoke, SaleItem_Reversal, SaleItem_DepositType, SaleItem_DepositCost, SaleItem_ActualCost, SaleItem_ListCost, SaleItem_SetID, SaleItem_WarehouseID ) SELECT M_SaleItem.SaleItemID, M_SaleItem.SaleItem_SaleID, M_SaleItem.SaleItem_StockItemID, M_SaleItem.SaleItem_ShrinkQuantity, M_SaleItem.SaleItem_Quantity, M_SaleItem.SaleItem_LineNo, M_SaleItem.SaleItem_Vat, M_SaleItem.SaleItem_PriceOriginal, M_SaleItem.SaleItem_Price, M_SaleItem.SaleItem_Revoke, M_SaleItem.SaleItem_Reversal, M_SaleItem.SaleItem_DepositType, M_SaleItem.SaleItem_DepositCost, M_SaleItem.SaleItem_ActualCost, M_SaleItem.SaleItem_ListCost, M_SaleItem.SaleItem_SetID, M_SaleItem.SaleItem_WarehouseID FROM M_SaleItem;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO SaleItemReciept ( SaleItemReciept_SaleItemID, SaleItemReciept_StockitemID, SaleItemReciept_Quantity, SaleItemReciept_DepositCost, SaleItemReciept_ListCost, SaleItemReciept_ActualCost, SaleItemReciept_Price ) SELECT M_SaleItemReciept.SaleItemReciept_SaleItemID, M_SaleItemReciept.SaleItemReciept_StockitemID, M_SaleItemReciept.SaleItemReciept_Quantity, M_SaleItemReciept.SaleItemReciept_DepositCost, M_SaleItemReciept.SaleItemReciept_ListCost, M_SaleItemReciept.SaleItemReciept_ActualCost, M_SaleItemReciept.SaleItemReciept_Price FROM M_SaleItemReciept;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_SaleItemReciept.* FROM M_SaleItemReciept;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_SaleItem.* FROM M_SaleItem;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_Sale.* FROM M_Sale;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO Supplier ( SupplierID, Supplier_SystemID, Supplier_Name, Supplier_PostalAddress, Supplier_PhysicalAddress, Supplier_Telephone, Supplier_Facimile, Supplier_RepresentativeName, Supplier_RepresentativeNumber, Supplier_ShippingCode, Supplier_OrderAttentionLine, Supplier_Terms, Supplier_Ullage, Supplier_discountCOD, Supplier_discount15days, Supplier_discount30days, Supplier_discount60days, Supplier_discount90days, Supplier_discount120days, Supplier_discountSmartCard, Supplier_discountDefault, Supplier_Current, Supplier_30Days, Supplier_60Days, Supplier_90Days, Supplier_120Days, Supplier_GRVtype ) ";
sql = sql + "SELECT M_Supplier.SupplierID, M_Supplier.Supplier_SystemID, M_Supplier.Supplier_Name, M_Supplier.Supplier_PostalAddress, M_Supplier.Supplier_PhysicalAddress, M_Supplier.Supplier_Telephone, M_Supplier.Supplier_Facimile, M_Supplier.Supplier_RepresentativeName, M_Supplier.Supplier_RepresentativeNumber, M_Supplier.Supplier_ShippingCode, M_Supplier.Supplier_OrderAttentionLine, M_Supplier.Supplier_Terms, M_Supplier.Supplier_Ullage, M_Supplier.Supplier_discountCOD, M_Supplier.Supplier_discount15days, M_Supplier.Supplier_discount30days, M_Supplier.Supplier_discount60days, M_Supplier.Supplier_discount90days, M_Supplier.Supplier_discount120days, M_Supplier.Supplier_discountSmartCard, M_Supplier.Supplier_discountDefault, M_Supplier.Supplier_Current, M_Supplier.Supplier_30Days, M_Supplier.Supplier_60Days, M_Supplier.Supplier_90Days, M_Supplier.Supplier_120Days, M_Supplier.Supplier_GRVtype FROM M_Supplier;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO SupplierTransaction ( SupplierTransactionID, SupplierTransaction_SupplierID, SupplierTransaction_PersonID, SupplierTransaction_TransactionTypeID, SupplierTransaction_MonthEndID, SupplierTransaction_MonthEndIDFor, SupplierTransaction_DayEndID, SupplierTransaction_ReferenceID, SupplierTransaction_Date, SupplierTransaction_Description, SupplierTransaction_Amount, SupplierTransaction_Reference ) ";
sql = sql + "SELECT M_SupplierTransaction.SupplierTransactionID, M_SupplierTransaction.SupplierTransaction_SupplierID, M_SupplierTransaction.SupplierTransaction_PersonID, M_SupplierTransaction.SupplierTransaction_TransactionTypeID, M_SupplierTransaction.SupplierTransaction_MonthEndID, M_SupplierTransaction.SupplierTransaction_MonthEndIDFor, M_SupplierTransaction.SupplierTransaction_DayEndID, M_SupplierTransaction.SupplierTransaction_ReferenceID, M_SupplierTransaction.SupplierTransaction_Date, M_SupplierTransaction.SupplierTransaction_Description, M_SupplierTransaction.SupplierTransaction_Amount, M_SupplierTransaction.SupplierTransaction_Reference FROM M_SupplierTransaction;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_SupplierTransaction.* FROM M_SupplierTransaction;";
dbcnnMonth.Execute(sql);
//*********************
sql = "INSERT INTO M_SupplierTransaction ( SupplierTransaction_SupplierID, SupplierTransaction_PersonID, SupplierTransaction_TransactionTypeID, SupplierTransaction_MonthEndID, SupplierTransaction_MonthEndIDFor, SupplierTransaction_DayEndID, SupplierTransaction_ReferenceID, SupplierTransaction_Date, SupplierTransaction_Description, SupplierTransaction_Amount, SupplierTransaction_Reference ) SELECT SupplierTransaction.SupplierTransaction_SupplierID, 1 AS person, 7 AS tranType, M_Company.Company_MonthEndID, M_Company.Company_MonthEndID, M_Company.Company_DayEndID, 0 AS refID, Now() AS [date], '' AS [desc], Sum(SupplierTransaction.SupplierTransaction_Amount) AS SumOfSupplierTransaction_Amount, 'Month End' AS ref From SupplierTransaction, M_Company GROUP BY SupplierTransaction.SupplierTransaction_SupplierID, M_Company.Company_MonthEndID, M_Company.Company_MonthEndID, M_Company.Company_DayEndID;";
dbcnnMonth.Execute(sql);
sql = "UPDATE M_Supplier SET M_Supplier.Supplier_120Days = [M_Supplier]![Supplier_120Days]+[M_Supplier]![Supplier_90Days], M_Supplier.Supplier_90Days = [M_Supplier]![Supplier_60Days], M_Supplier.Supplier_60Days = [M_Supplier]![Supplier_30Days], M_Supplier.Supplier_30Days = [M_Supplier]![Supplier_Current], M_Supplier.Supplier_Current = 0;";
dbcnnMonth.Execute(sql);
//Debtor Age shifting if Credit
//dbcnnMonth.Execute "UPDATE M_Supplier SET M_Supplier.Supplier_120Days = iif(([M_Supplier]![Supplier_150Days]<0),([M_Supplier]![Supplier_120Days]+[M_Supplier]![Supplier_150Days]),[M_Supplier]![Supplier_120Days]);"
//dbcnnMonth.Execute "UPDATE M_Supplier SET M_Supplier.Supplier_150Days = iif(([M_Supplier]![Supplier_150Days]<0),0,[M_Supplier]![Supplier_150Days]);"
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_90Days = iif(([M_Supplier]![Supplier_120Days]<0),([M_Supplier]![Supplier_90Days]+[M_Supplier]![Supplier_120Days]),[M_Supplier]![Supplier_90Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_120Days = iif(([M_Supplier]![Supplier_120Days]<0),0,[M_Supplier]![Supplier_120Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_60Days = iif(([M_Supplier]![Supplier_90Days]<0),([M_Supplier]![Supplier_60Days]+[M_Supplier]![Supplier_90Days]),[M_Supplier]![Supplier_60Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_90Days = iif(([M_Supplier]![Supplier_90Days]<0),0,[M_Supplier]![Supplier_90Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_30Days = iif(([M_Supplier]![Supplier_60Days]<0),([M_Supplier]![Supplier_30Days]+[M_Supplier]![Supplier_60Days]),[M_Supplier]![Supplier_30Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_60Days = iif(([M_Supplier]![Supplier_60Days]<0),0,[M_Supplier]![Supplier_60Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_Current = iif(([M_Supplier]![Supplier_30Days]<0),([M_Supplier]![Supplier_Current]+[M_Supplier]![Supplier_30Days]),[M_Supplier]![Supplier_Current]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_30Days = iif(([M_Supplier]![Supplier_30Days]<0),0,[M_Supplier]![Supplier_30Days]);");
//Debtor Age shifting if Credit
this.Cursor = System.Windows.Forms.Cursors.Default;
//fix Server Path in Month End DBs
ADODB.Recordset rsPOSList = default(ADODB.Recordset);
string strSvrName = null;
//Create a buffer
strSvrName = new string(Strings.Chr(0), 255);
//Get the computer name
GetComputerName(strSvrName, ref 255);
//remove the unnecessary chr$(0)'s
strSvrName = Strings.Left(strSvrName, Strings.InStr(1, strSvrName, Strings.Chr(0)));
strSvrName = Strings.Left(strSvrName, Strings.Len(strSvrName) - 1);
//MsgBox strSvrName
rsPOSList = modRecordSet.getRS(ref "SELECT * FROM POS;");
if (rsPOSList.RecordCount > 1) {
//if more then 1 POS
//Set rsMonthList = getRS("SELECT MonthEndID FROM MonthEnd;")
//If rsMonthList.RecordCount Then
// Do While rsMonthList.EOF = False
// databaseName = "Month" & rsMonthList("MonthEndID") & ".mdb"
if (fso.FileExists(modRecordSet.serverPath + databaseName)) {
buildPath1_Month(ref databaseName, ref strSvrName);
System.Windows.Forms.Application.DoEvents();
buildPath1_Month(ref databaseName, ref strSvrName);
}
// rsMonthList.moveNext
// Loop
//End If
}
//fix Server Path in Month End DBs
//If rs("Company_OpenDebtor") = True Then
//Else
// If MsgBox("Would you like to Enable 'OPEN DEBTOR' option from starting month?" & vbCrLf & vbCrLf & "NOTE: It is recommended to turn this option now if you wish to use." & vbCrLf & vbCrLf & "You can enable it later from 'Store Setup and Security -> General Parameters'.", vbYesNo) = vbYes Then
// sql = "UPDATE Company Set Company.Company_OpenDebtor = True;"
// cnnDB.Execute sql
// End If
//End If
//For Auto UpdatePOS on MonthEnd
if (Interaction.MsgBox("You are requested to do UpdatePOS at this stage, to run some Reports." + Constants.vbCrLf + Constants.vbCrLf + "NOTE: If you have changed Prices for some items, UpdatePOS will update Terminals." + Constants.vbCrLf + Constants.vbCrLf + "If you want to Run UpdatePOS now select 'YES' or click 'NO' If you don't want to change the prices on terminals.", MsgBoxStyle.YesNo) == MsgBoxResult.Yes) {
modApplication.blMEndUpdatePOS = true;
My.MyProject.Forms.frmUpdatePOScriteria.ShowDialog();
} else {
modApplication.blMEndUpdatePOS = false;
}
modApplication.blMEndUpdatePOS = false;
}
private void doMonthEnd()
{
string sql = null;
string lPath = null;
ADODB.Recordset rs = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
ADODB.Connection dbcnnMonth = default(ADODB.Connection);
//fix Server Path in Month End DBs
string databaseName = null;
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
System.Windows.Forms.Application.DoEvents();
//BuildAll
rs = modRecordSet.getRS(ref "SELECT Company_MonthEndID, Company_OpenDebtor From Company");
lPath = modRecordSet.serverPath + "month" + rs.Fields("Company_MonthEndID").Value + ".mdb";
//fix Server Path in Month End DBs
databaseName = "month" + rs.Fields("Company_MonthEndID").Value + ".mdb";
sql = "UPDATE Company Set Company.Company_MonthEndID = Company.Company_MonthEndID + 1;";
modRecordSet.cnnDB.Execute(sql);
sql = "INSERT INTO MonthEnd ( MonthEndID, MonthEnd_Date, MonthEnd_Days, MonthEnd_BudgetSales, MonthEnd_BudgetPurchases ) SELECT Company.Company_MonthEndID, Now(), 20, 100000, 100000 FROM Company;";
modRecordSet.cnnDB.Execute(sql);
modRecordSet.cnnDB.Execute("UPDATE Company INNER JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID SET DayEnd.DayEnd_MonthEndID = [Company]![Company_MonthEndID];");
fso.CopyFile(modRecordSet.serverPath + "template.mdb", lPath, true);
dbcnnMonth = new ADODB.Connection();
var _with1 = dbcnnMonth;
_with1.Provider = "Microsoft.ACE.OLEDB.12.0";
_with1.Properties("Jet OLEDB:System Database").Value = "" + modRecordSet.serverPath + "Secured.mdw";
_with1.Open(lPath, "liquid", "lqd");
sql = "UPDATE StockitemHistory SET StockitemHistory.StockitemHistory_Month12 = [StockitemHistory]![StockitemHistory_Month11], StockitemHistory.StockitemHistory_Month11 = [StockitemHistory]![StockitemHistory_Month10], StockitemHistory.StockitemHistory_Month10 = [StockitemHistory]![StockitemHistory_Month9], StockitemHistory.StockitemHistory_Month9 = [StockitemHistory]![StockitemHistory_Month8], StockitemHistory.StockitemHistory_Month8 = [StockitemHistory]![StockitemHistory_Month7], StockitemHistory.StockitemHistory_Month7 = [StockitemHistory]![StockitemHistory_Month6], StockitemHistory.StockitemHistory_Month6 = [StockitemHistory]![StockitemHistory_Month5], StockitemHistory.StockitemHistory_Month5 = [StockitemHistory]![StockitemHistory_Month4], ";
sql = sql + "StockitemHistory.StockitemHistory_Month4 = [StockitemHistory]![StockitemHistory_Month3], StockitemHistory.StockitemHistory_Month3 = [StockitemHistory]![StockitemHistory_Month2], StockitemHistory.StockitemHistory_Month2 = [StockitemHistory]![StockitemHistory_Month1], StockitemHistory.StockitemHistory_Month1 = 0;";
modRecordSet.cnnDB.Execute(sql);
sql = "INSERT INTO Customer ( CustomerID, Customer_ChannelID, Customer_InvoiceName, Customer_DepartmentName, Customer_FirstName, Customer_Surname, Customer_PhysicalAddress, Customer_PostalAddress, Customer_Telephone, Customer_Fax, Customer_Email, Customer_Disabled, Customer_Terms, Customer_CreditLimit, Customer_Current, Customer_30Days, Customer_60Days, Customer_90Days, Customer_120Days, Customer_150Days, Customer_PrintStatement,Customer_VATNumber ) ";
sql = sql + "SELECT M_Customer.CustomerID, M_Customer.Customer_ChannelID, M_Customer.Customer_InvoiceName, M_Customer.Customer_DepartmentName, M_Customer.Customer_FirstName, M_Customer.Customer_Surname, M_Customer.Customer_PhysicalAddress, M_Customer.Customer_PostalAddress, M_Customer.Customer_Telephone, M_Customer.Customer_Fax, M_Customer.Customer_Email, M_Customer.Customer_Disabled, M_Customer.Customer_Terms, M_Customer.Customer_CreditLimit, M_Customer.Customer_Current, M_Customer.Customer_30Days, M_Customer.Customer_60Days, M_Customer.Customer_90Days, M_Customer.Customer_120Days, M_Customer.Customer_150Days, M_Customer.Customer_PrintStatement, M_Customer.Customer_VATNumber FROM M_Customer;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO CustomerTransaction ( CustomerTransactionID, CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done, CustomerTransaction_Main, CustomerTransaction_Child, CustomerTransaction_Allocated ) ";
sql = sql + "SELECT M_CustomerTransaction.CustomerTransactionID, M_CustomerTransaction.CustomerTransaction_CustomerID, M_CustomerTransaction.CustomerTransaction_TransactionTypeID, M_CustomerTransaction.CustomerTransaction_DayEndID, M_CustomerTransaction.CustomerTransaction_MonthEndID, M_CustomerTransaction.CustomerTransaction_ReferenceID, M_CustomerTransaction.CustomerTransaction_Date, M_CustomerTransaction.CustomerTransaction_Description, M_CustomerTransaction.CustomerTransaction_Amount, M_CustomerTransaction.CustomerTransaction_Reference, M_CustomerTransaction.CustomerTransaction_PersonName, M_CustomerTransaction.CustomerTransaction_Done, M_CustomerTransaction.CustomerTransaction_Main, M_CustomerTransaction.CustomerTransaction_Child, M_CustomerTransaction.CustomerTransaction_Allocated FROM M_CustomerTransaction;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO CustomerTransactionAlloc ( CustomerTransactionAllocID, CustomerTransactionAlloc_CustomerID, CustomerTransactionAlloc_MainID, CustomerTransactionAlloc_ChildID, CustomerTransactionAlloc_Date, CustomerTransactionAlloc_Description, CustomerTransactionAlloc_Amount, CustomerTransactionAlloc_Reference, CustomerTransactionAlloc_PersonName ) ";
sql = sql + "SELECT M_CustomerTransactionAlloc.CustomerTransactionAllocID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_CustomerID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_MainID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_ChildID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Date, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Description, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Amount, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Reference, M_CustomerTransactionAlloc.CustomerTransactionAlloc_PersonName FROM M_CustomerTransactionAlloc;";
dbcnnMonth.Execute(sql);
if (rs.Fields("Company_OpenDebtor").Value == true)
{
//sql = "DELETE M_CustomerTransaction.* FROM M_CustomerTransaction WHERE (((M_CustomerTransaction.CustomerTransaction_Allocated)=[M_CustomerTransaction].[CustomerTransaction_Amount]) AND ((M_CustomerTransaction.CustomerTransaction_Allocated)<>0));"
//dbcnnMonth.Execute sql
sql = "DELETE M_CustomerTransaction.* FROM M_CustomerTransaction;";
dbcnnMonth.Execute(sql);
//version 1 problem
//sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done, CustomerTransaction_Main, CustomerTransaction_Child, CustomerTransaction_Allocated ) "
//sql = sql & "SELECT CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, (CustomerTransaction.CustomerTransaction_Amount-CustomerTransaction.CustomerTransaction_Allocated) AS SumOfCustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference & ' B/F' AS ref, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Done, CustomerTransaction.CustomerTransaction_Main, CustomerTransaction.CustomerTransaction_Child, CustomerTransaction.CustomerTransaction_Allocated "
//sql = sql & "From CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_Allocated)<>[CustomerTransaction].[CustomerTransaction_Amount])) AND (((CustomerTransaction.CustomerTransaction_Allocated)<>0));"
//dbcnnMonth.Execute sql
//version 2
//sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done ) "
//sql = sql & "SELECT CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, M_Company.Company_DayEndID, M_Company.Company_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, (CustomerTransaction.CustomerTransaction_Amount-CustomerTransaction.CustomerTransaction_Allocated) AS SumOfCustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference & '" & " *" & "' AS ref, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Done "
//sql = sql & "FROM CustomerTransaction, M_Company WHERE (((CustomerTransaction.CustomerTransaction_Allocated)<>[CustomerTransaction].[CustomerTransaction_Amount])) ORDER BY CustomerTransaction.CustomerTransactionID;"
//dbcnnMonth.Execute sql
//version 3
sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done ) ";
sql = sql + "SELECT CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, (CustomerTransaction.CustomerTransaction_Amount-CustomerTransaction.CustomerTransaction_Allocated) AS SumOfCustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference & '" + " *" + "' AS ref, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Done ";
sql = sql + "FROM CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_Allocated)<>[CustomerTransaction].[CustomerTransaction_Amount])) ORDER BY CustomerTransaction.CustomerTransactionID;";
dbcnnMonth.Execute(sql);
}
else
{
//DO THE OLD WAY
sql = "DELETE M_CustomerTransaction.* FROM M_CustomerTransaction;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName ) SELECT CustomerTransaction.CustomerTransaction_CustomerID, 7 AS transType, M_Company.Company_DayEndID, M_Company.Company_MonthEndID, 0 AS reference, Now() AS [date], '' AS [desc], Sum(CustomerTransaction.CustomerTransaction_Amount) AS SumOfCustomerTransaction_Amount, 'Month End' AS ref, 'System' AS person From CustomerTransaction, M_Company GROUP BY CustomerTransaction.CustomerTransaction_CustomerID, M_Company.Company_DayEndID, M_Company.Company_MonthEndID;";
dbcnnMonth.Execute(sql);
}
sql = "UPDATE M_Customer SET M_Customer.Customer_150Days = [M_Customer]![Customer_150Days]+[M_Customer]![Customer_120Days], M_Customer.Customer_120Days = [M_Customer]![Customer_90Days], M_Customer.Customer_90Days = [M_Customer]![Customer_60Days], M_Customer.Customer_60Days = [M_Customer]![Customer_30Days], M_Customer.Customer_30Days = [M_Customer]![Customer_Current], M_Customer.Customer_Current = 0;";
dbcnnMonth.Execute(sql);
//Debtor Age shifting if Credit
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_120Days = iif(([M_Customer]![Customer_150Days]<0),([M_Customer]![Customer_120Days]+[M_Customer]![Customer_150Days]),[M_Customer]![Customer_120Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_150Days = iif(([M_Customer]![Customer_150Days]<0),0,[M_Customer]![Customer_150Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_90Days = iif(([M_Customer]![Customer_120Days]<0),([M_Customer]![Customer_90Days]+[M_Customer]![Customer_120Days]),[M_Customer]![Customer_90Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_120Days = iif(([M_Customer]![Customer_120Days]<0),0,[M_Customer]![Customer_120Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_60Days = iif(([M_Customer]![Customer_90Days]<0),([M_Customer]![Customer_60Days]+[M_Customer]![Customer_90Days]),[M_Customer]![Customer_60Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_90Days = iif(([M_Customer]![Customer_90Days]<0),0,[M_Customer]![Customer_90Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_30Days = iif(([M_Customer]![Customer_60Days]<0),([M_Customer]![Customer_30Days]+[M_Customer]![Customer_60Days]),[M_Customer]![Customer_30Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_60Days = iif(([M_Customer]![Customer_60Days]<0),0,[M_Customer]![Customer_60Days]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_Current = iif(([M_Customer]![Customer_30Days]<0),([M_Customer]![Customer_Current]+[M_Customer]![Customer_30Days]),[M_Customer]![Customer_Current]);");
dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_30Days = iif(([M_Customer]![Customer_30Days]<0),0,[M_Customer]![Customer_30Days]);");
//Debtor Age shifting if Credit
//Tranfer change sql = "INSERT INTO DayEndStockItemLnk ( DayEndStockItemLnk_DayEndID, DayEndStockItemLnk_StockItemID, DayEndStockItemLnk_Quantity, DayEndStockItemLnk_QuantitySales, DayEndStockItemLnk_QuantityShrink, DayEndStockItemLnk_QuantityGRV, DayEndStockItemLnk_ListCost, DayEndStockItemLnk_ActualCost, DayEndStockItemLnk_Warehouse ) SELECT M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, M_DayEndStockItemLnk.DayEndStockItemLnk_StockItemID, M_DayEndStockItemLnk.DayEndStockItemLnk_Quantity, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityShrink, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV, M_DayEndStockItemLnk.DayEndStockItemLnk_ListCost, M_DayEndStockItemLnk.DayEndStockItemLnk_ActualCost, M_DayEndStockItemLnk.DayEndStockItemLnk_Warehouse From M_DayEndStockItemLnk, M_Company WHERE (((M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<>[M_Company]![Company_DayEndID]));"
sql = "INSERT INTO DayEndStockItemLnk ( DayEndStockItemLnk_DayEndID, DayEndStockItemLnk_StockItemID, DayEndStockItemLnk_Quantity, DayEndStockItemLnk_QuantitySales, DayEndStockItemLnk_QuantityShrink, DayEndStockItemLnk_QuantityGRV, DayEndStockItemLnk_QuantityTransafer, DayEndStockItemLnk_ListCost, DayEndStockItemLnk_ActualCost, DayEndStockItemLnk_Warehouse ) SELECT M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, M_DayEndStockItemLnk.DayEndStockItemLnk_StockItemID, M_DayEndStockItemLnk.DayEndStockItemLnk_Quantity, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityShrink, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityTransafer, M_DayEndStockItemLnk.DayEndStockItemLnk_ListCost, M_DayEndStockItemLnk.DayEndStockItemLnk_ActualCost, M_DayEndStockItemLnk.DayEndStockItemLnk_Warehouse ";
sql = sql + "From M_DayEndStockItemLnk, M_Company WHERE (((M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<>[M_Company]![Company_DayEndID]));";
dbcnnMonth.Execute(sql);
sql = "DELETE M_DayEndStockItemLnk.* From M_DayEndStockItemLnk, M_Company WHERE (((M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<>[M_Company]![Company_DayEndID]));";
dbcnnMonth.Execute(sql);
//Tranfer change
sql = "INSERT INTO StockTransferWH ( StockTransferWH_Date, StockTransferWH_DayEndID, StockTransferWH_PersonID, StockTransferWH_WHFrom, StockTransferWH_WHTo, StockTransferWH_StockItemID, StockTransferWH_Qty ) SELECT M_StockTransferWH.StockTransferWH_Date, M_StockTransferWH.StockTransferWH_DayEndID, M_StockTransferWH.StockTransferWH_PersonID, M_StockTransferWH.StockTransferWH_WHFrom, M_StockTransferWH.StockTransferWH_WHTo, M_StockTransferWH.StockTransferWH_StockItemID, M_StockTransferWH.StockTransferWH_Qty ";
sql = sql + "From M_StockTransferWH, M_Company WHERE (((M_StockTransferWH.StockTransferWH_DayEndID)<>[M_Company]![Company_DayEndID]));";
dbcnnMonth.Execute(sql);
sql = "DELETE M_StockTransferWH.* From M_StockTransferWH, M_Company WHERE (((M_StockTransferWH.StockTransferWH_DayEndID)<>[M_Company]![Company_DayEndID]));";
dbcnnMonth.Execute(sql);
//Tranfer change
sql = "INSERT INTO Declaration ( DeclarationID, Declaration_POSID, Declaration_DayEndID, Declaration_Date, Declaration_Cash, Declaration_CashServer, Declaration_CashCount, Declaration_Cheque, Declaration_ChequeServer, Declaration_ChequeCount, Declaration_Card, Declaration_CardServer, Declaration_CardCount, Declaration_Payout, Declaration_PayoutServer, Declaration_PayoutCount, Declaration_Total, Declaration_TotalServer, Declaration_TotalCount ) ";
sql = sql + "SELECT M_Declaration.DeclarationID, M_Declaration.Declaration_POSID, M_Declaration.Declaration_DayEndID, M_Declaration.Declaration_Date, M_Declaration.Declaration_Cash, M_Declaration.Declaration_CashServer, M_Declaration.Declaration_CashCount, M_Declaration.Declaration_Cheque, M_Declaration.Declaration_ChequeServer, M_Declaration.Declaration_ChequeCount, M_Declaration.Declaration_Card, M_Declaration.Declaration_CardServer, M_Declaration.Declaration_CardCount, M_Declaration.Declaration_Payout, M_Declaration.Declaration_PayoutServer, M_Declaration.Declaration_PayoutCount, M_Declaration.Declaration_Total, M_Declaration.Declaration_TotalServer, M_Declaration.Declaration_TotalCount FROM M_Declaration;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_Declaration.* FROM M_Declaration;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO Sale ( SaleID, Sale_PosID, Sale_DeclarationID, Sale_ChannelID, Sale_PersonID, Sale_ManagerID, Sale_DayEndID, Sale_Date, Sale_DatePOS, Sale_SubTotal, Sale_Discount, Sale_Total, Sale_Tender, Sale_Slip, Sale_PaymentType, Sale_Reference,Sale_CardRef,Sale_OrderRef,Sale_SerialRef,Sale_Cash,Sale_Card,Sale_Cheque,Sale_CDebit,Sale_PersonShiftID,Sale_TableNumber,Sale_GuestCount,Sale_SlipCount,Sale_Gratuity,Sale_DisChk,Sale_SaleChk ) ";
sql = sql + "SELECT M_Sale.SaleID, M_Sale.Sale_PosID, M_Sale.Sale_DeclarationID, M_Sale.Sale_ChannelID, M_Sale.Sale_PersonID, M_Sale.Sale_ManagerID, M_Sale.Sale_DayEndID, M_Sale.Sale_Date, M_Sale.Sale_DatePOS, M_Sale.Sale_SubTotal, M_Sale.Sale_Discount, M_Sale.Sale_Total, M_Sale.Sale_Tender, M_Sale.Sale_Slip, M_Sale.Sale_PaymentType, Sale_Reference,M_Sale.Sale_CardRef,M_Sale.Sale_OrderRef,M_Sale.Sale_SerialRef, M_Sale.Sale_Cash,M_Sale.Sale_Card,M_Sale.Sale_Cheque,M_Sale.Sale_CDebit,M_Sale.Sale_PersonShiftID,M_Sale.Sale_TableNumber,M_Sale.Sale_GuestCount,M_Sale.Sale_SlipCount,M_Sale.Sale_Gratuity,M_Sale.Sale_DisChk,M_Sale.Sale_SaleChk FROM M_Sale;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO SaleItem ( SaleItemID, SaleItem_SaleID, SaleItem_StockItemID, SaleItem_ShrinkQuantity, SaleItem_Quantity, SaleItem_LineNo, SaleItem_Vat, SaleItem_PriceOriginal, SaleItem_Price, SaleItem_Revoke, SaleItem_Reversal, SaleItem_DepositType, SaleItem_DepositCost, SaleItem_ActualCost, SaleItem_ListCost, SaleItem_SetID, SaleItem_WarehouseID ) SELECT M_SaleItem.SaleItemID, M_SaleItem.SaleItem_SaleID, M_SaleItem.SaleItem_StockItemID, M_SaleItem.SaleItem_ShrinkQuantity, M_SaleItem.SaleItem_Quantity, M_SaleItem.SaleItem_LineNo, M_SaleItem.SaleItem_Vat, M_SaleItem.SaleItem_PriceOriginal, M_SaleItem.SaleItem_Price, M_SaleItem.SaleItem_Revoke, M_SaleItem.SaleItem_Reversal, M_SaleItem.SaleItem_DepositType, M_SaleItem.SaleItem_DepositCost, M_SaleItem.SaleItem_ActualCost, M_SaleItem.SaleItem_ListCost, M_SaleItem.SaleItem_SetID, M_SaleItem.SaleItem_WarehouseID FROM M_SaleItem;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO SaleItemReciept ( SaleItemReciept_SaleItemID, SaleItemReciept_StockitemID, SaleItemReciept_Quantity, SaleItemReciept_DepositCost, SaleItemReciept_ListCost, SaleItemReciept_ActualCost, SaleItemReciept_Price ) SELECT M_SaleItemReciept.SaleItemReciept_SaleItemID, M_SaleItemReciept.SaleItemReciept_StockitemID, M_SaleItemReciept.SaleItemReciept_Quantity, M_SaleItemReciept.SaleItemReciept_DepositCost, M_SaleItemReciept.SaleItemReciept_ListCost, M_SaleItemReciept.SaleItemReciept_ActualCost, M_SaleItemReciept.SaleItemReciept_Price FROM M_SaleItemReciept;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_SaleItemReciept.* FROM M_SaleItemReciept;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_SaleItem.* FROM M_SaleItem;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_Sale.* FROM M_Sale;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO Supplier ( SupplierID, Supplier_SystemID, Supplier_Name, Supplier_PostalAddress, Supplier_PhysicalAddress, Supplier_Telephone, Supplier_Facimile, Supplier_RepresentativeName, Supplier_RepresentativeNumber, Supplier_ShippingCode, Supplier_OrderAttentionLine, Supplier_Terms, Supplier_Ullage, Supplier_discountCOD, Supplier_discount15days, Supplier_discount30days, Supplier_discount60days, Supplier_discount90days, Supplier_discount120days, Supplier_discountSmartCard, Supplier_discountDefault, Supplier_Current, Supplier_30Days, Supplier_60Days, Supplier_90Days, Supplier_120Days, Supplier_GRVtype ) ";
sql = sql + "SELECT M_Supplier.SupplierID, M_Supplier.Supplier_SystemID, M_Supplier.Supplier_Name, M_Supplier.Supplier_PostalAddress, M_Supplier.Supplier_PhysicalAddress, M_Supplier.Supplier_Telephone, M_Supplier.Supplier_Facimile, M_Supplier.Supplier_RepresentativeName, M_Supplier.Supplier_RepresentativeNumber, M_Supplier.Supplier_ShippingCode, M_Supplier.Supplier_OrderAttentionLine, M_Supplier.Supplier_Terms, M_Supplier.Supplier_Ullage, M_Supplier.Supplier_discountCOD, M_Supplier.Supplier_discount15days, M_Supplier.Supplier_discount30days, M_Supplier.Supplier_discount60days, M_Supplier.Supplier_discount90days, M_Supplier.Supplier_discount120days, M_Supplier.Supplier_discountSmartCard, M_Supplier.Supplier_discountDefault, M_Supplier.Supplier_Current, M_Supplier.Supplier_30Days, M_Supplier.Supplier_60Days, M_Supplier.Supplier_90Days, M_Supplier.Supplier_120Days, M_Supplier.Supplier_GRVtype FROM M_Supplier;";
dbcnnMonth.Execute(sql);
sql = "INSERT INTO SupplierTransaction ( SupplierTransactionID, SupplierTransaction_SupplierID, SupplierTransaction_PersonID, SupplierTransaction_TransactionTypeID, SupplierTransaction_MonthEndID, SupplierTransaction_MonthEndIDFor, SupplierTransaction_DayEndID, SupplierTransaction_ReferenceID, SupplierTransaction_Date, SupplierTransaction_Description, SupplierTransaction_Amount, SupplierTransaction_Reference ) ";
sql = sql + "SELECT M_SupplierTransaction.SupplierTransactionID, M_SupplierTransaction.SupplierTransaction_SupplierID, M_SupplierTransaction.SupplierTransaction_PersonID, M_SupplierTransaction.SupplierTransaction_TransactionTypeID, M_SupplierTransaction.SupplierTransaction_MonthEndID, M_SupplierTransaction.SupplierTransaction_MonthEndIDFor, M_SupplierTransaction.SupplierTransaction_DayEndID, M_SupplierTransaction.SupplierTransaction_ReferenceID, M_SupplierTransaction.SupplierTransaction_Date, M_SupplierTransaction.SupplierTransaction_Description, M_SupplierTransaction.SupplierTransaction_Amount, M_SupplierTransaction.SupplierTransaction_Reference FROM M_SupplierTransaction;";
dbcnnMonth.Execute(sql);
sql = "DELETE M_SupplierTransaction.* FROM M_SupplierTransaction;";
dbcnnMonth.Execute(sql);
//*********************
sql = "INSERT INTO M_SupplierTransaction ( SupplierTransaction_SupplierID, SupplierTransaction_PersonID, SupplierTransaction_TransactionTypeID, SupplierTransaction_MonthEndID, SupplierTransaction_MonthEndIDFor, SupplierTransaction_DayEndID, SupplierTransaction_ReferenceID, SupplierTransaction_Date, SupplierTransaction_Description, SupplierTransaction_Amount, SupplierTransaction_Reference ) SELECT SupplierTransaction.SupplierTransaction_SupplierID, 1 AS person, 7 AS tranType, M_Company.Company_MonthEndID, M_Company.Company_MonthEndID, M_Company.Company_DayEndID, 0 AS refID, Now() AS [date], '' AS [desc], Sum(SupplierTransaction.SupplierTransaction_Amount) AS SumOfSupplierTransaction_Amount, 'Month End' AS ref From SupplierTransaction, M_Company GROUP BY SupplierTransaction.SupplierTransaction_SupplierID, M_Company.Company_MonthEndID, M_Company.Company_MonthEndID, M_Company.Company_DayEndID;";
dbcnnMonth.Execute(sql);
sql = "UPDATE M_Supplier SET M_Supplier.Supplier_120Days = [M_Supplier]![Supplier_120Days]+[M_Supplier]![Supplier_90Days], M_Supplier.Supplier_90Days = [M_Supplier]![Supplier_60Days], M_Supplier.Supplier_60Days = [M_Supplier]![Supplier_30Days], M_Supplier.Supplier_30Days = [M_Supplier]![Supplier_Current], M_Supplier.Supplier_Current = 0;";
dbcnnMonth.Execute(sql);
//Debtor Age shifting if Credit
//dbcnnMonth.Execute "UPDATE M_Supplier SET M_Supplier.Supplier_120Days = iif(([M_Supplier]![Supplier_150Days]<0),([M_Supplier]![Supplier_120Days]+[M_Supplier]![Supplier_150Days]),[M_Supplier]![Supplier_120Days]);"
//dbcnnMonth.Execute "UPDATE M_Supplier SET M_Supplier.Supplier_150Days = iif(([M_Supplier]![Supplier_150Days]<0),0,[M_Supplier]![Supplier_150Days]);"
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_90Days = iif(([M_Supplier]![Supplier_120Days]<0),([M_Supplier]![Supplier_90Days]+[M_Supplier]![Supplier_120Days]),[M_Supplier]![Supplier_90Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_120Days = iif(([M_Supplier]![Supplier_120Days]<0),0,[M_Supplier]![Supplier_120Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_60Days = iif(([M_Supplier]![Supplier_90Days]<0),([M_Supplier]![Supplier_60Days]+[M_Supplier]![Supplier_90Days]),[M_Supplier]![Supplier_60Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_90Days = iif(([M_Supplier]![Supplier_90Days]<0),0,[M_Supplier]![Supplier_90Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_30Days = iif(([M_Supplier]![Supplier_60Days]<0),([M_Supplier]![Supplier_30Days]+[M_Supplier]![Supplier_60Days]),[M_Supplier]![Supplier_30Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_60Days = iif(([M_Supplier]![Supplier_60Days]<0),0,[M_Supplier]![Supplier_60Days]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_Current = iif(([M_Supplier]![Supplier_30Days]<0),([M_Supplier]![Supplier_Current]+[M_Supplier]![Supplier_30Days]),[M_Supplier]![Supplier_Current]);");
dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_30Days = iif(([M_Supplier]![Supplier_30Days]<0),0,[M_Supplier]![Supplier_30Days]);");
//Debtor Age shifting if Credit
this.Cursor = System.Windows.Forms.Cursors.Default;
//fix Server Path in Month End DBs
ADODB.Recordset rsPOSList = default(ADODB.Recordset);
string strSvrName = null;
//Create a buffer
strSvrName = new string(Strings.Chr(0), 255);
//Get the computer name
GetComputerName(strSvrName, ref 255);
//remove the unnecessary chr$(0)'s
strSvrName = Strings.Left(strSvrName, Strings.InStr(1, strSvrName, Strings.Chr(0)));
strSvrName = Strings.Left(strSvrName, Strings.Len(strSvrName) - 1);
//MsgBox strSvrName
rsPOSList = modRecordSet.getRS(ref "SELECT * FROM POS;");
if (rsPOSList.RecordCount > 1)
{
//if more then 1 POS
//Set rsMonthList = getRS("SELECT MonthEndID FROM MonthEnd;")
//If rsMonthList.RecordCount Then
// Do While rsMonthList.EOF = False
// databaseName = "Month" & rsMonthList("MonthEndID") & ".mdb"
if (fso.FileExists(modRecordSet.serverPath + databaseName))
{
buildPath1_Month(ref databaseName, ref strSvrName);
System.Windows.Forms.Application.DoEvents();
buildPath1_Month(ref databaseName, ref strSvrName);
}
// rsMonthList.moveNext
// Loop
//End If
}
//fix Server Path in Month End DBs
//If rs("Company_OpenDebtor") = True Then
//Else
// If MsgBox("Would you like to Enable 'OPEN DEBTOR' option from starting month?" & vbCrLf & vbCrLf & "NOTE: It is recommended to turn this option now if you wish to use." & vbCrLf & vbCrLf & "You can enable it later from 'Store Setup and Security -> General Parameters'.", vbYesNo) = vbYes Then
// sql = "UPDATE Company Set Company.Company_OpenDebtor = True;"
// cnnDB.Execute sql
// End If
//End If
//For Auto UpdatePOS on MonthEnd
if (Interaction.MsgBox("You are requested to do UpdatePOS at this stage, to run some Reports." + Constants.vbCrLf + Constants.vbCrLf + "NOTE: If you have changed Prices for some items, UpdatePOS will update Terminals." + Constants.vbCrLf + Constants.vbCrLf + "If you want to Run UpdatePOS now select 'YES' or click 'NO' If you don't want to change the prices on terminals.", MsgBoxStyle.YesNo) == MsgBoxResult.Yes)
{
modApplication.blMEndUpdatePOS = true;
My.MyProject.Forms.frmUpdatePOScriteria.ShowDialog();
}
else
{
modApplication.blMEndUpdatePOS = false;
}
modApplication.blMEndUpdatePOS = false;
}
private bool updateProc()
{
bool functionReturnValue = false;
decimal AsHoldOriginal = default(decimal);
int gID = 0;
string strFldName = null;
// ERROR: Not supported in C#: OnErrorStatement
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rj = default(ADODB.Recordset);
ADODB.Recordset RSadoPrimary = default(ADODB.Recordset);
ADODB.Recordset rsBarcode = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
string sql = null;
decimal lQuantity = default(decimal);
int getNewID = 0;
//Set rs = getRS("SELECT VegTestItem.*, StockItem.StockItemID, StockItem.StockItem_SBarcode FROM VegTestItem INNER JOIN StockItem ON VegTestItem.VegTestItem_StockItemID = StockItem.StockItemID WHERE (((VegTestItem.VegTestItem_VegTestID)=" & testID & "));")
//If rs.RecordCount > 0 Then
strFldName = "HandHeldID Number,Handheld_Barcode Text(50), Quantity Currency";
modRecordSet.cnnDB.Execute("CREATE TABLE " + "HandheldVegTest" + " (" + strFldName + ")");
System.Windows.Forms.Application.DoEvents();
sql = "INSERT INTO HandheldVegTest (HandHeldID,Handheld_Barcode,Quantity) VALUES (" + gStockItemID + ", 0, " + (0 - (Convert.ToDecimal(txtQtyTaken.Text) - Convert.ToDecimal(txtQty.Text))) + ")";
modRecordSet.cnnDB.Execute(sql);
System.Windows.Forms.Application.DoEvents();
// Do While rs.EOF = False
// If rs("VegTestItem_PerWeightYield") > 0 Then
// getNewID = 0
// If rs("StockItem_SBarcode") = True Then
// getNewID = rs("VegTestItem_StockItemID")
// 'create new
// 'CreateVegItems rs("VegTestItem_StockItemID"), rs("VegTestItem_ActualSellPriceIncl"), rs("VegTestItem_PackSize"), getNewID ' csvSplit(0), csvSplit(1), csvSplit(2), CCur(csvSplit(3)), CCur(csvSplit(4)), csvSplit(6), csvSplit(7)
// Else
// getNewID = rs("VegTestItem_StockItemID")
// End If
// 'Stock Adjustment
// sql = "INSERT INTO HandheldVegTest (HandHeldID,Handheld_Barcode,Quantity) VALUES (" & getNewID & ", 0, " & (rs("VegTestItem_PerWeightYield")) & ")"
// cnnDB.Execute sql
// End If
// rs.moveNext
// Loop
//Else
// MsgBox "This Product does not have any Recipe.", vbApplicationModal + vbInformation + vbOKOnly, App.title
// Exit Function
//End If
//---------------------------------------------
modRecordSet.cnnDB.Execute("INSERT INTO StockGroup (StockGroup_Name) VALUES ('HandheldVegTest')");
modApplication.stTableName = "HandheldVegTest";
rj = modRecordSet.getRS(ref "SELECT StockGroup.StockGroupID, StockGroup.StockGroup_Name From StockGroup WHERE StockGroup.StockGroup_Name = 'HandheldVegTest';");
gID = rj.Fields("StockGroupID").Value;
//snap shot
modRecordSet.cnnDB.Execute("UPDATE Company SET Company.Company_StockTakeDate = now();");
//Multi Warehouse change
modRecordSet.cnnDB.Execute("DELETE FROM StockTake WHERE (StockTake_WarehouseID > 0)");
modRecordSet.cnnDB.Execute("INSERT INTO StockTake ( StockTake_StockItemID, StockTake_WarehouseID, StockTake_Quantity, StockTake_Adjustment ) SELECT WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID, WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID, 0 AS quantity, 0 AS adjustment FROM WarehouseStockItemLnk;");
modRecordSet.cnnDB.Execute("UPDATE StockTake INNER JOIN WarehouseStockItemLnk ON (StockTake.StockTake_StockItemID = WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID) AND (StockTake.StockTake_WarehouseID = WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID) SET StockTake.StockTake_Quantity = [WarehouseStockItemLnk]![WarehouseStockItemLnk_Quantity] WHERE (((WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)>0));");
//Multi Warehouse change
// cnnDB.Execute "UPDATE StockTake SET StockTake.StockTake_Adjustment = [StockTake]![StockTake_Quantity];"
modRecordSet.cnnDB.Execute("DELETE FROM StockTakeDeposit");
modRecordSet.cnnDB.Execute("INSERT INTO StockTakeDeposit ( StockTakeDeposit_WarehouseID, StockTakeDeposit_DepositID, StockTakeDeposit_DepositTypeID, StockTakeDeposit_Quantity, StockTakeDeposit_Adjustment ) SELECT WarehouseDepositItemLnk.WarehouseDepositItemLnk_WarehouseID, WarehouseDepositItemLnk.WarehouseDepositItemLnk_DepositID, WarehouseDepositItemLnk.WarehouseDepositItemLnk_DepositTypeID, WarehouseDepositItemLnk.WarehouseDepositItemLnk_Quantity, WarehouseDepositItemLnk.WarehouseDepositItemLnk_Quantity FROM WarehouseDepositItemLnk INNER JOIN DISPLAY_Deposits ON (DISPLAY_Deposits.type = WarehouseDepositItemLnk.WarehouseDepositItemLnk_DepositTypeID) AND (WarehouseDepositItemLnk.WarehouseDepositItemLnk_DepositID = DISPLAY_Deposits.DepositID) AND (WarehouseDepositItemLnk.WarehouseDepositItemLnk_WarehouseID = DISPLAY_Deposits.WarehouseID);");
//snap shot
RSadoPrimary = modRecordSet.getRS(ref "SELECT StockItem.StockItem_Name, " + modApplication.stTableName + ".Quantity, StockItem.StockItem_Quantity," + modApplication.stTableName + ".HandHeldID, StockTake.StockTake_Quantity, StockTake.StockTake_StockItemID FROM ((" + modApplication.stTableName + " INNER JOIN StockItem ON " + modApplication.stTableName + ".HandHeldID = StockItem.StockItemID) INNER JOIN StockGroup ON StockItem.StockItem_StockGroupID = StockGroup.StockGroupID) INNER JOIN (StockTake INNER JOIN Warehouse ON StockTake.StockTake_WarehouseID = Warehouse.WarehouseID) ON StockItem.StockItemID = StockTake.StockTake_StockItemID Where (((StockGroup.StockGroupID) < " + gID + ") And ((Warehouse.WarehouseID) = 2)) ORDER BY StockItem.StockItem_Name");
if (RSadoPrimary.RecordCount > 0)
{
while (RSadoPrimary.EOF == false)
{
AsHoldOriginal = RSadoPrimary.Fields("StockTake_Adjustment").Value;
//Setting the StockTake_Adjustment to it's original value
RSadoPrimary.Fields("StockTake_Adjustment").Value = 0;
lQuantity = AsHoldOriginal + RSadoPrimary.Fields("StockTake_Quantity").OriginalValue;
lQuantity = RSadoPrimary.Fields("Quantity").Value;
lQuantity = RSadoPrimary.Fields("Quantity").Value + RSadoPrimary.Fields("StockTake_Quantity").OriginalValue;
modRecordSet.cnnDB.Execute("INSERT INTO StockTakeDetail ( StockTake_StockItemID, StockTake_WarehouseID, StockTake_Quantity, StockTake_Adjustment, StockTake_DayEndID, StockTake_Note, StockTake_DateTime ) SELECT " + RSadoPrimary.Fields("StockTake_StockItemID").Value + ", 2, " + lQuantity + ", " + RSadoPrimary.Fields("Quantity").Value + ", Company_DayEndID, '" + "4VEG Repair" + " ', #" + DateAndTime.Now + "# FROM Company;");
modRecordSet.cnnDB.Execute("UPDATE StockTake SET StockTake.StockTake_Quantity = " + lQuantity + " WHERE (((StockTake.StockTake_StockItemID)=" + RSadoPrimary.Fields("StockTake_StockItemID").Value + ") AND ((StockTake.StockTake_WarehouseID)=2));");
//cnnDB.Execute "UPDATE WarehouseStockItemLnk INNER JOIN StockTake ON (StockTake.StockTake_WarehouseID = WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID) AND (WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = StockTake.StockTake_StockItemID) SET WarehouseStockItemLnk.WarehouseStockItemLnk_Quantity = [WarehouseStockItemLnk]![WarehouseStockItemLnk_Quantity]-(" & lQuantity & ") WHERE (((StockTake.StockTake_StockItemID)=" & RSadoPrimary("StockTake_StockItemID") & ") AND ((StockTake.StockTake_WarehouseID)=2));"
modRecordSet.cnnDB.Execute("UPDATE WarehouseStockItemLnk INNER JOIN StockTake ON (StockTake.StockTake_WarehouseID = WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID) AND (WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = StockTake.StockTake_StockItemID) SET WarehouseStockItemLnk.WarehouseStockItemLnk_Quantity = " + lQuantity + " WHERE (((StockTake.StockTake_StockItemID)=" + RSadoPrimary.Fields("StockTake_StockItemID").Value + ") AND ((StockTake.StockTake_WarehouseID)=2));");
//cnnDB.Execute "UPDATE WarehouseStockItemLnk INNER JOIN StockTake ON (StockTake.StockTake_WarehouseID = WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID) AND (WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = StockTake.StockTake_StockItemID) SET WarehouseStockItemLnk.WarehouseStockItemLnk_Quantity = 0 WHERE (((StockTake.StockTake_StockItemID)=" & RSadoPrimary("StockTake_StockItemID") & ") AND ((StockTake.StockTake_WarehouseID)=2));"
modRecordSet.cnnDB.Execute("UPDATE DayEndStockItemLnk INNER JOIN Company ON DayEndStockItemLnk.DayEndStockItemLnk_DayEndID = Company.Company_DayEndID SET DayEndStockItemLnk.DayEndStockItemLnk_QuantityShrink = [DayEndStockItemLnk]![DayEndStockItemLnk_QuantityShrink]-(" + RSadoPrimary.Fields("Quantity").Value + ") WHERE (((DayEndStockItemLnk.DayEndStockItemLnk_StockItemID)=" + RSadoPrimary.Fields("StockTake_StockItemID").Value + "));");
RSadoPrimary.moveNext();
}
}
//Update POS
//Set rsPri = getRS("SELECT GRVItem.GRVItem_StockItemID, (GRVItem.GRVItem_Quantity*GRVItem_PackSize) AS GRVItem_Quantity, StockItem.StockItem_SBarcode,StockItem.StockItem_SShelf FROM GRVItem INNER JOIN StockItem ON GRVItem.GRVItem_StockItemID = StockItem.StockItemID WHERE GRVItem_GRVID = " & Val(frmGRV.adoPrimaryRS("GRVID")) & " AND (StockItem_SBarcode = True Or StockItem_SShelf = True)")
//Set rsBarcode = getRS("SELECT HandheldVegTest.HandHeldID AS GRVItem_StockItemID, HandheldVegTest.Quantity AS GRVItem_Quantity, StockItem.StockItem_SBarcode, StockItem.StockItem_SShelf FROM HandheldVegTest INNER JOIN StockItem ON HandheldVegTest.HandHeldID = StockItem.StockItemID WHERE (((HandheldVegTest.Quantity)>0));")
rsBarcode = modRecordSet.getRS(ref "SELECT HandheldVegTest.HandHeldID AS GRVItem_StockItemID, " + Convert.ToDecimal(txtQty.Text) + " AS GRVItem_Quantity, StockItem.StockItem_SBarcode, StockItem.StockItem_SShelf FROM HandheldVegTest INNER JOIN StockItem ON HandheldVegTest.HandHeldID = StockItem.StockItemID WHERE (((HandheldVegTest.Quantity)<>0) AND ((StockItem.StockItem_SBarcode)=True)) OR (((StockItem.StockItem_SShelf)=True));");
//Write file
if (rsBarcode.RecordCount)
{
if (fso.FileExists(modRecordSet.serverPath + "ShelfBarcode.dat"))
{
fso.DeleteFile(modRecordSet.serverPath + "ShelfBarcode.dat", true);
}
rsBarcode.save(modRecordSet.serverPath + "ShelfBarcode.dat", ADODB.PersistFormatEnum.adPersistADTG);
modApplication.grvPrin = true;
//If MsgBox("Do you want to do Shelf/Barcode Printing on flagged StockItems?", vbQuestion + vbYesNo + vbApplicationModal + vbDefaultButton1, App.title) = vbYes Then
modApplication.blMEndUpdatePOS = true;
modApplication.blChangeOnlyUpdatePOS = true;
My.MyProject.Forms.frmUpdatePOScriteria.ShowDialog();
modApplication.blChangeOnlyUpdatePOS = false;
modApplication.blMEndUpdatePOS = false;
My.MyProject.Forms.frmBarcode.ShowDialog();
}
modRecordSet.cnnDB.Execute("DROP TABLE HandheldVegTest");
modRecordSet.cnnDB.Execute("DELETE * FROM StockGroup WHERE StockGroup_Name ='HandheldVegTest'");
//cnnDB.Execute "UPDATE VegTest SET VegTest_VegTestStatusID = 3 WHERE (VegTestID = " & testID & ")"
//cnnDB.Execute "UPDATE VegTest INNER JOIN GRVItem ON (VegTest.VegTest_GRVID = GRVItem.GRVItem_GRVID) AND (VegTest.VegTest_MainItemID = GRVItem.GRVItem_StockItemID) SET GRVItem.GRVItem_QuantityUsedKG = " & CCur(TotalQTY.Text) & " WHERE (((VegTest.VegTestID)=" & testID & "));"
Interaction.MsgBox("Repair process has been completed.", MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, _4PosBackOffice.NET.My.MyProject.Application.Info.Title);
functionReturnValue = true;
return(functionReturnValue);
UpdateErr:
Interaction.MsgBox(Err().Description);
// ERROR: Not supported in C#: ResumeStatement
return(functionReturnValue);
//updateProc = True
}
public static ADODB.Connection openConnectionInstance(ref string lDatabase = "")
{
ADODB.Connection functionReturnValue = default(ADODB.Connection);
string Path = null;
Scripting.FileSystemObject FSO = new Scripting.FileSystemObject();
if (string.IsNullOrEmpty(lDatabase))
{
lDatabase = "Pricing.mdb";
}
if (string.IsNullOrEmpty(serverPath))
{
setServerPath();
}
// ERROR: Not supported in C#: OnErrorStatement
ADODB.Connection cn = new ADODB.Connection();
//UPGRADE_NOTE: Object openConnectionInstance may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
functionReturnValue = null;
string strDBPath = null;
if (string.IsNullOrEmpty(serverPath))
{
}
else
{
strDBPath = serverPath + lDatabase;
var _with2 = cn;
_with2.Provider = "Microsoft.ACE.OLEDB.12.0";
_with2.Properties("Jet OLEDB:System Database").Value = serverPath + "Secured.mdw";
_with2.Open(strDBPath, "liquid", "lqd");
functionReturnValue = cn;
}
return(functionReturnValue);
withPass:
if (string.IsNullOrEmpty(serverPath))
{
setNewServerPath();
}
if (!string.IsNullOrEmpty(serverPath))
{
functionReturnValue = null;
strDBPath = serverPath + lDatabase;
Path = strDBPath + ";Jet " + "OLEDB:Database Password=lqd";
cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source= " + Path);
functionReturnValue = cn;
}
return(functionReturnValue);
openConnection_Error:
if (Err().Description == "[Microsoft][ODBC Microsoft Access Driver] Not a valid password.")
{
goto withPass;
}
else if (Err().Description == "Not a valid password.")
{
goto withPass;
}
else
{
Interaction.MsgBox(Err().Number + " - " + Err().Description);
}
return(functionReturnValue);
}
private bool buildPath1_Month(ref string lPath, ref string lServerPath)
{
bool functionReturnValue = false;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table tbl = new ADOX.Table();
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Connection cn = default(ADODB.Connection);
string lFile = null;
string holdfile = null;
short x = 0;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
string lDir = null;
// ERROR: Not supported in C#: OnErrorStatement
Cursor = System.Windows.Forms.Cursors.WaitCursor;
//lPath = upgradePath
System.Windows.Forms.Application.DoEvents();
lDir = Strings.LCase("\\\\" + lServerPath + "\\C\\4posServer\\");
cn = modRecordSet.openConnectionInstance(ref lPath);
if (cn == null)
{
}
else
{
cat.let_ActiveConnection(cn);
foreach (tbl in cat.Tables)
{
if (tbl.Type == "LINK")
{
System.Windows.Forms.Application.DoEvents();
//lFile = tbl.Name
if (tbl.Properties("Jet OLEDB:Link Datasource").Value != lDir + "pricing.mdb")
{
tbl.Properties("Jet OLEDB:Link Datasource").Value = Strings.Replace(Strings.LCase(tbl.Properties("Jet OLEDB:Link Datasource").Value), Strings.LCase("C:\\4posServer\\"), lDir);
}
//DoEvents
//If tbl.Properties("Jet OLEDB:Link Datasource") <> lDIR & "pricing.mdb" Then
// tbl.Properties("Jet OLEDB:Link Datasource") = Replace(LCase(tbl.Properties("Jet OLEDB:Link Datasource")), LCase("C:\4posServer\"), lDIR)
//End If
}
}
//UPGRADE_NOTE: Object cat may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
cat = null;
cn.Close();
//UPGRADE_NOTE: Object cn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
cn = null;
cat = new ADOX.Catalog();
}
System.Windows.Forms.Application.DoEvents();
Cursor = System.Windows.Forms.Cursors.Default;
functionReturnValue = true;
return(functionReturnValue);
buildPath_Error:
Cursor = System.Windows.Forms.Cursors.Default;
Interaction.MsgBox(Err().Description);
functionReturnValue = false;
return(functionReturnValue);
}
public static bool openConnection()
{
bool functionReturnValue = false;
string Path = null;
string cnnDBname = null;
//this
// ERROR: Not supported in C#: OnErrorStatement
//If Win7Ver() = True Then
//Path = "c:\4posserver\"
//Else
//Path = String.Empty
//End If
cnnDBname = "4pos";
cnnDB = new ADODB.Connection();
//cnnDB.Provider = "Microsoft.ACE.OLEDB.12.0"
cnnDB.Open(cnnDBname);
//win 7
Scripting.FileSystemObject FSO = new Scripting.FileSystemObject();
Scripting.TextStream textstream = default(Scripting.TextStream);
string lString = null;
if (modWinVer.Win7Ver() == true)
{
if (FSO.FileExists("C:\\4POS\\4POSWinPath.txt"))
{
textstream = FSO.OpenTextFile("C:\\4POS\\4POSWinPath.txt", Scripting.IOMode.ForReading, true);
lString = textstream.ReadAll;
serverPath = lString;
//& "pricing.mdb"
}
else
{
serverPath = "C:\\4POSServer\\";
//"pricing.mdb"
}
functionReturnValue = true;
return(functionReturnValue);
}
//win 7
serverPath = Strings.Split(Strings.Split(cnnDB.ConnectionString, ";DBQ=")[1], ";")[0];
serverPath = Strings.Split(Strings.LCase(serverPath), "pricing.mdb")[0];
//
functionReturnValue = true;
return(functionReturnValue);
if (string.IsNullOrEmpty(serverPath))
{
setServerPath();
}
string strDBPath = null;
if (string.IsNullOrEmpty(serverPath))
{
functionReturnValue = false;
}
else
{
functionReturnValue = true;
cnnDB = new ADODB.Connection();
strDBPath = serverPath + "Pricing.mdb";
var _with1 = cnnDB;
_with1.Provider = "Microsoft.ACE.OLEDB.12.0";
_with1.Properties("Jet OLEDB:System Database").Value = serverPath + "secured.mdw";
_with1.Open(strDBPath, "liquid", "lqd");
}
return(functionReturnValue);
withPass:
if (string.IsNullOrEmpty(serverPath))
{
setNewServerPath();
}
if (!string.IsNullOrEmpty(serverPath))
{
cnnDB = new ADODB.Connection();
strDBPath = serverPath + "pricing.mdb";
Path = strDBPath + ";Jet " + "OLEDB:Database Password=lqd";
cnnDB.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cnnDB.Open("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source= " + Path);
functionReturnValue = true;
}
return(functionReturnValue);
openConnection_Error:
if (Err().Description == "[Microsoft][ODBC Microsoft Access Driver] Not a valid password.")
{
goto withPass;
}
else if (Err().Description == "Not a valid password.")
{
goto withPass;
}
functionReturnValue = false;
return(functionReturnValue);
}
private void linkTables(ref string source)
{
ADOX.Catalog cat = default(ADOX.Catalog);
ADOX.Table tbl = default(ADOX.Table);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
if (fso.FileExists(modRecordSet.serverPath + source + ".mdb"))
{
}
else
{
return;
}
cat = new ADOX.Catalog();
short x = 0;
// Open the catalog.
cat.let_ActiveConnection(modReport.cnnDBreport);
for (x = cat.Tables.Count - 1; x >= 0; x += -1)
{
switch (Strings.LCase(cat.Tables(x).name))
{
case "acustomertransaction":
case "adayendstockitemlnk":
case "adeclaration":
case "asale":
case "asaleitem":
case "asuppliertransaction":
cat.Tables.delete(cat.Tables(x).name);
break;
}
}
tbl = new ADOX.Table();
tbl.name = "aCustomerTransaction";
tbl.ParentCatalog = cat;
tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb";
tbl.Properties("Jet OLEDB:Remote Table Name").Value = "CustomerTransaction";
tbl.Properties("Jet OLEDB:Create Link").Value = true;
cat.Tables.Append(tbl);
tbl = new ADOX.Table();
tbl.name = "aDayEndStockItemLnk";
tbl.ParentCatalog = cat;
tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb";
tbl.Properties("Jet OLEDB:Remote Table Name").Value = "DayEndStockItemLnk";
tbl.Properties("Jet OLEDB:Create Link").Value = true;
cat.Tables.Append(tbl);
tbl = new ADOX.Table();
tbl.name = "aDeclaration";
tbl.ParentCatalog = cat;
tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb";
tbl.Properties("Jet OLEDB:Remote Table Name").Value = "Declaration";
tbl.Properties("Jet OLEDB:Create Link").Value = true;
cat.Tables.Append(tbl);
tbl = new ADOX.Table();
tbl.name = "aSale";
tbl.ParentCatalog = cat;
tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb";
tbl.Properties("Jet OLEDB:Remote Table Name").Value = "Sale";
tbl.Properties("Jet OLEDB:Create Link").Value = true;
cat.Tables.Append(tbl);
tbl = new ADOX.Table();
tbl.name = "aSaleItem";
tbl.ParentCatalog = cat;
tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb";
tbl.Properties("Jet OLEDB:Remote Table Name").Value = "SaleItem";
tbl.Properties("Jet OLEDB:Create Link").Value = true;
cat.Tables.Append(tbl);
tbl = new ADOX.Table();
tbl.name = "aSupplierTransaction";
tbl.ParentCatalog = cat;
tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb";
tbl.Properties("Jet OLEDB:Remote Table Name").Value = "SupplierTransaction";
tbl.Properties("Jet OLEDB:Create Link").Value = true;
cat.Tables.Append(tbl);
cat.Tables.Refresh();
cat = null;
}
private void beginUpdate()
{
bool gModeReport = false;
// ERROR: Not supported in C#: OnErrorStatement
picInner.Width = 0;
gCNT = 0;
System.Windows.Forms.Application.DoEvents();
short x = 0;
string sql = null;
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsChk = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
bool lMode = false;
gModeReport = false;
if (Strings.Left(modRecordSet.serverPath, 3) == "c:\\")
{
}
else
{
buildPath1(ref modRecordSet.serverPath);
}
Label1.Text = "Loading Report ...";
//Dim ccndbReport As Connection
string[] lTable = null;
short y = 0;
lTable = Strings.Split("aChannel,aCompany,aConsignment,aCustomer,aDayEnd,aDayEndDepositItemLnk,aDeposit,aftConstruct,aftData,aftDataItem,aftSet,aGRV,aGRVDeposit,aGRVitem,aPackSize,aPayout,aPerson,aPOS,aPricingGroup,aPurchaseOrder,aRecipe,aRecipeStockitemLnk,aSaleItemReciept,aShrink,aStockBreakTransaction,aStockGroup,aStockItem,aStockTakeDetail,Supplier,Vat", ",");
System.Windows.Forms.Application.DoEvents();
gTotal = 9 + 1 * 9;
//ccndbReport.Close
//Set ccndbReport = openConnectionInstance("templatereport.mdb")
for (y = 0; y <= Information.UBound(lTable); y++)
{
moveItem();
System.Windows.Forms.Application.DoEvents();
modReport.cnnDBreport.Execute("DELETE * FROM " + lTable[y] + ";");
modReport.cnnDBreport.Execute("INSERT INTO " + lTable[y] + " SELECT * FROM " + lTable[y] + "1;");
}
Label1.Text = "Updating Report Data ...";
picInner.Width = 0;
gCNT = 0;
System.Windows.Forms.Application.DoEvents();
modReport.cnnDBreport.Execute("DELETE * FROM DayEnd");
modReport.cnnDBreport.Execute("INSERT INTO dayend ( DayEndID, DayEnd_MonthEndID, DayEnd_Date ) SELECT aDayEnd.DayEndID, aDayEnd.DayEnd_MonthEndID, aDayEnd.DayEnd_Date From aDayEnd, Report WHERE (((aDayEnd.DayEndID)>=[Report]![Report_DayEndStartID] And (aDayEnd.DayEndID)<=[Report]![Report_DayEndEndID]));");
modReport.cnnDBreport.Execute("DELETE DayEndStockItemLnkFirst.* FROM DayEndStockItemLnkFirst;");
//Error in aCompany showing old wrong data if month end is done
//Set rs = getRSreport("SELECT aDayEnd.*, aCompany.Company_DayEndID, aCompany.Company_MonthEndID From aDayEnd, Report, aCompany WHERE (((aDayEnd.DayEndID)=[Report]![Report_DayEndStartID]-1));")
rs = modReport.getRSreport(ref "SELECT aDayEnd.*, aCompany1.Company_DayEndID, aCompany1.Company_MonthEndID From aDayEnd, Report, aCompany1 WHERE (((aDayEnd.DayEndID)=[Report]![Report_DayEndStartID]-1));");
if (rs.RecordCount)
{
if (rs.Fields("DayEnd_MonthEndID").Value == rs.Fields("Company_MonthEndID").Value)
{
linkFirstTable(ref "pricing");
}
else
{
linkFirstTable(ref "month" + rs.Fields("DayEnd_MonthEndID").Value);
}
modReport.cnnDBreport.Execute("INSERT INTO DayEndStockItemLnkFirst SELECT aDayEndStockItemLnk.* FROM Report, aDayEndStockItemLnk INNER JOIN aDayEnd ON aDayEndStockItemLnk.DayEndStockItemLnk_DayEndID = aDayEnd.DayEndID WHERE (((aDayEnd.DayEndID)=[Report]![Report_DayEndStartID]-1));");
}
rs.Close();
//Error in aCompany showing old wrong data if month end is done
//Set rs = getRSreport("SELECT DISTINCT DayEnd.DayEnd_MonthEndID, aCompany.Company_MonthEndID FROM DayEnd, aCompany;")
rs = modReport.getRSreport(ref "SELECT DISTINCT DayEnd.DayEnd_MonthEndID, aCompany1.Company_MonthEndID FROM DayEnd, aCompany1;");
gTotal = 9 + rs.RecordCount * 9;
moveItem();
modReport.cnnDBreport.Execute("DELETE Payout.* FROM Payout;");
modReport.cnnDBreport.Execute("INSERT INTO Payout SELECT aPayout.* From Report, aPayout WHERE (((aPayout.Payout_DayEndID)>=[Report]![Report_DayEndStartID] And (aPayout.Payout_DayEndID)<=[Report]![Report_DayEndEndID]));");
moveItem();
modReport.cnnDBreport.Execute("DELETE aStockTakeDetail.* FROM aStockTakeDetail;");
modReport.cnnDBreport.Execute("INSERT INTO aStockTakeDetail SELECT aStockTakeDetail1.* From Report, aStockTakeDetail1 WHERE (((aStockTakeDetail1.StockTake_DayEndID)>=[Report]![Report_DayEndStartID] And (aStockTakeDetail1.StockTake_DayEndID)<=[Report]![Report_DayEndEndID]));");
moveItem();
modReport.cnnDBreport.Execute("DELETE * FROM DayEndStockItemLnk");
moveItem();
modReport.cnnDBreport.Execute("DELETE * FROM DayEndDepositItemLnk");
moveItem();
modReport.cnnDBreport.Execute("DELETE SaleItem.* FROM SaleItem;");
moveItem();
//consignment setting in report
modReport.cnnDBreport.Execute("DELETE aConsignment.* FROM aConsignment;");
moveItem();
modReport.cnnDBreport.Execute("DELETE Sale.* FROM Sale;");
moveItem();
modReport.cnnDBreport.Execute("DELETE CustomerTransaction.* FROM CustomerTransaction;");
moveItem();
modReport.cnnDBreport.Execute("DELETE Declaration.* FROM Declaration;");
moveItem();
modReport.cnnDBreport.Execute("DELETE SupplierTransaction.* FROM SupplierTransaction;");
moveItem();
while (!(rs.EOF))
{
if (rs.Fields("DayEnd_MonthEndID").Value == rs.Fields("Company_MonthEndID").Value)
{
linkTables(ref "pricing");
}
else
{
linkTables(ref "month" + rs.Fields("DayEnd_MonthEndID").Value);
}
moveItem();
modReport.cnnDBreport.Execute("INSERT INTO DayEndStockItemLnk SELECT aDayEndStockItemLnk.* FROM aDayEndStockItemLnk INNER JOIN DayEnd ON aDayEndStockItemLnk.DayEndStockItemLnk_DayEndID = DayEnd.DayEndID;");
moveItem();
// ERROR: Not supported in C#: OnErrorStatement
modReport.cnnDBreport.Execute("INSERT INTO DayEndDepositItemLnk SELECT aDayEndDepositItemLnk.* FROM aDayEndDepositItemLnk INNER JOIN DayEnd ON aDayEndDepositItemLnk.DayEndDepositItemLnk_DayEndID = DayEnd.DayEndID;");
moveItem();
if (My.MyProject.Forms.frmMenu.gSuper == true)
{
modReport.cnnDBreport.Execute("INSERT INTO sale SELECT aSale.* FROM [SELECT aSale.* FROM aSale LEFT JOIN Sale ON aSale.SaleID = Sale.SaleID WHERE (((Sale.SaleID) Is Null))]. AS aSale, Report WHERE (((aSale.Sale_DayEndID)>=[Report]![Report_DayEndStartID] And (aSale.Sale_DayEndID)<=[Report]![Report_DayEndEndID]));");
}
else
{
if (rsChk.State)
{
rsChk.Close();
}
rsChk = modReport.getRSreport(ref "SELECT TOP 1 * FROM aSale;");
if (rsChk.Fields.Count < 30)
{
modReport.cnnDBreport.Execute("INSERT INTO sale SELECT aSale.* FROM [SELECT aSale.* FROM aSale LEFT JOIN Sale ON aSale.SaleID = Sale.SaleID WHERE (((Sale.SaleID) Is Null))]. AS aSale, Report WHERE (((aSale.Sale_DayEndID)>=[Report]![Report_DayEndStartID] And (aSale.Sale_DayEndID)<=[Report]![Report_DayEndEndID]));");
}
else
{
modReport.cnnDBreport.Execute("INSERT INTO sale SELECT aSale.* FROM [SELECT aSale.* FROM aSale LEFT JOIN Sale ON aSale.SaleID = Sale.SaleID WHERE (((Sale.SaleID) Is Null))]. AS aSale, Report WHERE ((((aSale.Sale_SaleChk)=False) And (aSale.Sale_DayEndID)>=[Report]![Report_DayEndStartID] And (aSale.Sale_DayEndID)<=[Report]![Report_DayEndEndID]));");
}
rsChk.Close();
}
moveItem();
modReport.cnnDBreport.Execute("INSERT INTO SaleItem SELECT aSaleItem.* FROM (aSaleItem INNER JOIN Sale ON aSaleItem.SaleItem_SaleID = Sale.SaleID) LEFT JOIN SaleItem ON aSaleItem.SaleItemID = SaleItem.SaleItemID WHERE (((SaleItem.SaleItemID) Is Null));");
moveItem();
//consignment setting in report
modReport.cnnDBreport.Execute("INSERT INTO aConsignment SELECT aConsignment1.* FROM aConsignment1;");
moveItem();
if (rs.Fields("DayEnd_MonthEndID").Value == rs.Fields("Company_MonthEndID").Value)
{
modReport.cnnDBreport.Execute("INSERT INTO CustomerTransaction SELECT aCustomerTransaction.* From Report, aCustomerTransaction WHERE (((aCustomerTransaction.CustomerTransaction_MonthEndID)=" + rs.Fields("DayEnd_MonthEndID").Value + ") AND ((aCustomerTransaction.CustomerTransaction_DayEndID)>=[Report]![Report_DayEndStartID] And (aCustomerTransaction.CustomerTransaction_DayEndID)<=[Report]![Report_DayEndEndID]));");
}
else
{
modReport.cnnDBreport.Execute("INSERT INTO CustomerTransaction SELECT aCustomerTransaction.* From Report, aCustomerTransaction WHERE (((aCustomerTransaction.CustomerTransaction_MonthEndID)=" + rs.Fields("DayEnd_MonthEndID").Value + ") AND ((aCustomerTransaction.CustomerTransaction_DayEndID)>=[Report]![Report_DayEndStartID] And (aCustomerTransaction.CustomerTransaction_DayEndID)<=[Report]![Report_DayEndEndID]));");
}
moveItem();
sql = "INSERT INTO SupplierTransaction ( SupplierTransactionID, SupplierTransaction_SupplierID, SupplierTransaction_PersonID, SupplierTransaction_TransactionTypeID, SupplierTransaction_MonthEndID, SupplierTransaction_MonthEndIDFor, SupplierTransaction_DayEndID, SupplierTransaction_ReferenceID, SupplierTransaction_Date, SupplierTransaction_Description, SupplierTransaction_Amount, SupplierTransaction_Reference )";
sql = sql + "SELECT aSupplierTransaction.SupplierTransactionID, aSupplierTransaction.SupplierTransaction_SupplierID, aSupplierTransaction.SupplierTransaction_PersonID, aSupplierTransaction.SupplierTransaction_TransactionTypeID, aSupplierTransaction.SupplierTransaction_MonthEndID, aSupplierTransaction.SupplierTransaction_MonthEndIDFor, aSupplierTransaction.SupplierTransaction_DayEndID, aSupplierTransaction.SupplierTransaction_ReferenceID, aSupplierTransaction.SupplierTransaction_Date, aSupplierTransaction.SupplierTransaction_Description, aSupplierTransaction.SupplierTransaction_Amount, aSupplierTransaction.SupplierTransaction_Reference FROM aSupplierTransaction INNER JOIN DayEnd ON aSupplierTransaction.SupplierTransaction_DayEndID = DayEnd.DayEndID;";
sql = "INSERT INTO SupplierTransaction ( SupplierTransactionID, SupplierTransaction_SupplierID, SupplierTransaction_PersonID, SupplierTransaction_TransactionTypeID, SupplierTransaction_MonthEndID, SupplierTransaction_MonthEndIDFor, SupplierTransaction_DayEndID, SupplierTransaction_ReferenceID, SupplierTransaction_Date, SupplierTransaction_Description, SupplierTransaction_Amount, SupplierTransaction_Reference ) ";
sql = sql + "SELECT [SupplierTransaction_MonthEndID] & [SupplierTransactionID] AS Expr1, aSupplierTransaction.SupplierTransaction_SupplierID, aSupplierTransaction.SupplierTransaction_PersonID, aSupplierTransaction.SupplierTransaction_TransactionTypeID, aSupplierTransaction.SupplierTransaction_MonthEndID, aSupplierTransaction.SupplierTransaction_MonthEndIDFor, aSupplierTransaction.SupplierTransaction_DayEndID, aSupplierTransaction.SupplierTransaction_ReferenceID, aSupplierTransaction.SupplierTransaction_Date, aSupplierTransaction.SupplierTransaction_Description, aSupplierTransaction.SupplierTransaction_Amount, aSupplierTransaction.SupplierTransaction_Reference FROM aSupplierTransaction INNER JOIN DayEnd ON aSupplierTransaction.SupplierTransaction_DayEndID = DayEnd.DayEndID;";
modReport.cnnDBreport.Execute(sql);
moveItem();
modReport.cnnDBreport.Execute("INSERT INTO Declaration SELECT aDeclaration.* From Report, aDeclaration WHERE (((aDeclaration.Declaration_DayEndID)>=[Report]![Report_DayEndStartID] And (aDeclaration.Declaration_DayEndID)<=[Report]![Report_DayEndEndID]));");
moveItem();
modReport.cnnDBreport.Execute("UPDATE aCompany INNER JOIN (DayEndStockItemLnk INNER JOIN aStockItem ON DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = aStockItem.StockItemID) ON aCompany.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID SET DayEndStockItemLnk.DayEndStockItemLnk_ListCost = [aStockItem]![StockItem_ListCost]/[aStockItem]![StockItem_Quantity], DayEndStockItemLnk.DayEndStockItemLnk_ActualCost = [aStockItem]![StockItem_ActualCost]/[aStockItem]![StockItem_Quantity];");
moveItem();
rs.moveNext();
}
moveItem();
modReport.cnnDBreport.Execute("UPDATE Report SET Report.Report_Type = " + lMode + ";");
rs = modReport.getRSreport(ref "SELECT DayEnd.DayEndID FROM aCompany INNER JOIN DayEnd ON aCompany.Company_DayEndID = DayEnd.DayEndID;");
if (rs.RecordCount)
{
modReport.cnnDBreport.Execute("UPDATE Report SET Report.Report_Heading = [Report_Heading] & '(*)';");
modReport.cnnDBreport.Execute("UPDATE aCompany INNER JOIN DayEndStockItemLnk ON aCompany.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID SET DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales = 0, DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV = 0;");
//Multi Warehouse change
//cnnDBreport.Execute "UPDATE aConsignment AS aConsignment_1 RIGHT JOIN (aConsignment RIGHT JOIN ((DayEndStockItemLnk INNER JOIN (Sale INNER JOIN aCompany ON Sale.Sale_DayEndID = aCompany.Company_DayEndID) ON DayEndStockItemLnk.DayEndStockItemLnk_DayEndID = aCompany.Company_DayEndID) INNER JOIN SaleItem ON (DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = SaleItem.SaleItem_StockItemID) AND (Sale.SaleID = SaleItem.SaleItem_SaleID)) ON aConsignment.Consignment_SaleID = Sale.SaleID) ON aConsignment_1.Consignment_ReversalSaleID = Sale.SaleID SET DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales = [DayEndStockItemLnk]![DayEndStockItemLnk_QuantitySales]+(IIf([SaleItem_Reversal],0-[SaleItem_ShrinkQuantity]*[SaleItem_Quantity],[SaleItem_ShrinkQuantity]*[SaleItem_Quantity])) WHERE (((SaleItem.SaleItem_Recipe)=0) AND ((SaleItem.SaleItem_Revoke)=0) AND ((SaleItem.SaleItem_DepositType)=0) AND ((aConsignment.ConsignmentID) Is Null) AND ((aConsignment_1.ConsignmentID) Is Null));"
sql = "UPDATE aConsignment AS aConsignment_1 RIGHT JOIN (aConsignment RIGHT JOIN ((DayEndStockItemLnk INNER JOIN (Sale INNER JOIN aCompany ON Sale.Sale_DayEndID = aCompany.Company_DayEndID) ON DayEndStockItemLnk.DayEndStockItemLnk_DayEndID = aCompany.Company_DayEndID) INNER JOIN SaleItem ON (DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = SaleItem.SaleItem_StockItemID) AND (DayEndStockItemLnk.DayEndStockItemLnk_Warehouse = SaleItem.SaleItem_WarehouseID) AND (Sale.SaleID = SaleItem.SaleItem_SaleID)) ON aConsignment.Consignment_SaleID = Sale.SaleID) ON aConsignment_1.Consignment_ReversalSaleID = Sale.SaleID SET DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales = [DayEndStockItemLnk]![DayEndStockItemLnk_QuantitySales]+(IIf([SaleItem_Reversal],0-[SaleItem_ShrinkQuantity]*[SaleItem_Quantity],[SaleItem_ShrinkQuantity]*[SaleItem_Quantity])) ";
sql = sql + "WHERE (((SaleItem.SaleItem_Recipe)=0) AND ((SaleItem.SaleItem_Revoke)=0) AND ((SaleItem.SaleItem_DepositType)=0) AND ((aConsignment.ConsignmentID) Is Null) AND ((aConsignment_1.ConsignmentID) Is Null));";
modReport.cnnDBreport.Execute(sql);
//sql = "UPDATE aSaleItemReciept INNER JOIN (aConsignment AS aConsignment_1 RIGHT JOIN (aConsignment RIGHT JOIN ((DayEndStockItemLnk INNER JOIN (Sale INNER JOIN aCompany ON Sale.Sale_DayEndID = aCompany.Company_DayEndID) ON DayEndStockItemLnk.DayEndStockItemLnk_DayEndID = aCompany.Company_DayEndID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleItem_SaleID) ON aConsignment.Consignment_SaleID = Sale.SaleID) ON aConsignment_1.Consignment_ReversalSaleID = Sale.SaleID) ON (aSaleItemReciept.SaleItemReciept_StockitemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID) AND (aSaleItemReciept.SaleItemReciept_SaleItemID = SaleItem.SaleItemID) SET DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales = [DayEndStockItemLnk]![DayEndStockItemLnk_QuantitySales]+(IIf([SaleItem_Reversal],0-[SaleItemReciept_Quantity]*[SaleItem_Quantity],[SaleItemReciept_Quantity]*[SaleItem_Quantity])) "
//sql = sql & "WHERE (((SaleItem.SaleItem_Recipe)<>0) AND ((SaleItem.SaleItem_Revoke)=0) AND ((SaleItem.SaleItem_DepositType)=0) AND ((aConsignment.ConsignmentID) Is Null) AND ((aConsignment_1.ConsignmentID) Is Null));"
sql = "UPDATE aSaleItemReciept INNER JOIN (aConsignment AS aConsignment_1 RIGHT JOIN (aConsignment RIGHT JOIN ((DayEndStockItemLnk INNER JOIN (Sale INNER JOIN aCompany ON Sale.Sale_DayEndID = aCompany.Company_DayEndID) ON DayEndStockItemLnk.DayEndStockItemLnk_DayEndID = aCompany.Company_DayEndID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleItem_SaleID AND DayEndStockItemLnk.DayEndStockItemLnk_Warehouse = SaleItem.SaleItem_WarehouseID) ON aConsignment.Consignment_SaleID = Sale.SaleID) ON aConsignment_1.Consignment_ReversalSaleID = Sale.SaleID) ON (aSaleItemReciept.SaleItemReciept_StockitemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID) AND (aSaleItemReciept.SaleItemReciept_SaleItemID = SaleItem.SaleItemID) SET DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales = [DayEndStockItemLnk]![DayEndStockItemLnk_QuantitySales]+(IIf([SaleItem_Reversal],0-[SaleItemReciept_Quantity]*[SaleItem_Quantity],[SaleItemReciept_Quantity]*[SaleItem_Quantity])) ";
sql = sql + "WHERE (((SaleItem.SaleItem_Recipe)<>0) AND ((SaleItem.SaleItem_Revoke)=0) AND ((SaleItem.SaleItem_DepositType)=0) AND ((aConsignment.ConsignmentID) Is Null) AND ((aConsignment_1.ConsignmentID) Is Null));";
modReport.cnnDBreport.Execute(sql);
//Multi Warehouse change
modReport.cnnDBreport.Execute("UPDATE aGRV INNER JOIN ((aCompany INNER JOIN DayEndStockItemLnk ON aCompany.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID) INNER JOIN aGRVItem ON DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = aGRVItem.GRVItem_StockItemID) ON (DayEndStockItemLnk.DayEndStockItemLnk_DayEndID = aGRV.GRV_DayEndID) AND (aGRV.GRVID = aGRVItem.GRVItem_GRVID) SET DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV = [DayEndStockItemLnk_QuantityGRV]+IIf([aGRVItem]![GRVItem_Return],0-[GRVItem_PackSize]*[GRVItem_Quantity],[GRVItem_PackSize]*[GRVItem_Quantity]) WHERE (((aGRV.GRV_GRVStatusID)=3));");
}
System.Windows.Forms.Application.DoEvents();
this.Close();
return;
Err_beginUpdate:
Interaction.MsgBox("Error while Loading Report and Error is :" + Err().Number + " " + Err().Description + " " + Err().Source);
System.Windows.Forms.Application.DoEvents();
this.Close();
}
public static string GetSerialNumber(string Drive)
{
Scripting.FileSystemObject SDrive = new Scripting.FileSystemObject();
return(SDrive.DriveExists(Drive) ? SDrive.Drives[Drive].SerialNumber.ToString() : "");
}
private void doMode(ref short mode)
{
// Dim lNodeList As IXMLDOMNodeList
gMode = mode;
// Dim lNode As IXMLDOMNode
ADODB.Recordset rs = default(ADODB.Recordset);
short x = 0;
for (x = 0; x <= frmMode.Count - 1; x++)
{
frmMode[x].Visible = false;
}
frmMode[gMode].Left = frmMode[0].Left;
frmMode[gMode].Top = frmMode[0].Top;
frmMode[gMode].Visible = true;
ADODB.Recordset rsME = default(ADODB.Recordset);
Scripting.TextStream textstreamME = default(Scripting.TextStream);
Scripting.FileSystemObject fsoME = new Scripting.FileSystemObject();
switch (gMode)
{
case mdDayEnd:
rs = modRecordSet.getRS(ref "SELECT id FROM (SELECT COUNT(*) AS id FROM company INNER JOIN Sale ON Company.Company_DayEndID = Sale.Sale_DayEndID) transactions");
if (rs.Fields("id").Value)
{
}
else
{
doMode(ref mdTransactions);
return;
}
this.cmdNext.Enabled = false;
break;
case mdTransactions:
rs = modRecordSet.getRS(ref "SELECT Count(Sale.SaleID) AS id FROM Company INNER JOIN ((Sale INNER JOIN DayEnd ON Sale.Sale_DayEndID = DayEnd.DayEndID) INNER JOIN MonthEnd ON DayEnd.DayEnd_MonthEndID = MonthEnd.MonthEndID) ON Company.Company_MonthEndID = MonthEnd.MonthEndID;");
if (rs.Fields("id").Value)
{
doMode(ref mdConfirm);
return;
}
//If blMontheEnd = True Then
// str1 = "Month End Run failed to run, please try again!!!" & vbCrLf & "If the same problem persist, Consult 4POS for assistance"
// MsgBox str1, vbApplicationModal + vbInformation + vbOKOnly, "Month End"
//End If
this.cmdNext.Enabled = false;
break;
case mdConfirm:
break;
case mdComplete:
//Set lDOM = modGeneral.lsData.SQL("p4_monthEnd")
//modSecurity.checkSecurity
//do backup
rsME = modRecordSet.getRS(ref "select Company_BackupPath from Company");
if (rsME.RecordCount)
{
if (!string.IsNullOrEmpty(rsME.Fields("Company_BackupPath").Value))
{
if (fsoME.FileExists("C:\\4POSBackup.txt"))
{
fsoME.DeleteFile("C:\\4POSBackup.txt", true);
}
textstreamME = fsoME.OpenTextFile("C:\\4POSBackup.txt", Scripting.IOMode.ForWriting, true);
textstreamME.Write(rsME.Fields("Company_BackupPath").Value);
textstreamME.Close();
Interaction.Shell("c:\\4pos\\4posbackup.exe", AppWinStyle.NormalFocus);
System.Windows.Forms.Application.DoEvents();
}
}
//do backup
this.cmdNext.Visible = false;
System.Windows.Forms.Application.DoEvents();
doMonthEnd();
break;
}
}
private void cmdLoad_Click(System.Object eventSender, System.EventArgs eventArgs)
{
string dbText = null;
string sql = null;
string stFileName = null;
string lOrder = null;
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsData = default(ADODB.Recordset);
//Exporting file...
string lne = null;
short n = 0;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
switch (this.cmbSortField.SelectedIndex) {
case 0:
lOrder = "StockItem_Name";
break;
case 1:
lOrder = "[exclusiveSum]-[depositSum]-[listCostSum]";
break;
case 2:
lOrder = "[exclusiveSum]-[depositSum]";
break;
case 3:
lOrder = "[exclusiveSum]-[depositSum]-[listCostSum]";
break;
case 4:
lOrder = "IIf([exclusiveSum]=0,0,([exclusiveSum]-[depositSum]-[listCostSum])/[exclusiveSum])";
break;
case 5:
lOrder = "StockList.quantitySum";
break;
}
if (this.cmbSort.SelectedIndex)
lOrder = lOrder + " DESC";
lOrder = " ORDER BY " + lOrder;
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modReport.getRSreport(ref "SELECT * FROM Link Where LinkID=2 AND Link_SectionID=1");
if (_optType_0.Checked) {
sql = "SELECT aStockItem.StockItemID, aStockItem.StockItem_Name, StockList.inclusiveSum AS inclusive, StockList.exclusiveSum AS exclusive, [exclusiveSum]-[depositSum] AS price, [exclusiveSum]-[depositSum] AS content, [exclusiveSum]-[depositSum]-[listCostSum] AS listProfit, [exclusiveSum]-[depositSum]-[actualCostSum] AS actualProfit, StockList.quantitySum AS quantity, StockList.listCostSum AS listCost, StockList.actualCostSum AS actualCost, IIf([exclusiveSum],([exclusiveSum]-[depositSum]-[actualCostSum])/[exclusiveSum]*100,0) AS gpActual, IIf([exclusiveSum],([exclusiveSum]-[depositSum]-[listCostSum])/[exclusiveSum]*100,0) AS gpList, aStockGroup.StockGroup_Name AS department FROM StockList INNER JOIN (aStockItem INNER JOIN aStockGroup ON aStockItem.StockItem_StockGroupID = aStockGroup.StockGroupID) ON StockList.SaleItem_StockItemID = aStockItem.StockItemID ";
} else {
sql = "SELECT aStockItem.StockItemID, aStockItem.StockItem_Name, StockList.inclusiveSum AS inclusive, StockList.exclusiveSum AS exclusive, [exclusiveSum]-[depositSum] AS price, [exclusiveSum]-[depositSum] AS content, [exclusiveSum]-[depositSum]-[listCostSum] AS listProfit, [exclusiveSum]-[depositSum]-[actualCostSum] AS actualProfit, StockList.quantitySum AS quantity, StockList.listCostSum AS listCost, StockList.actualCostSum AS actualCost, IIf([exclusiveSum],([exclusiveSum]-[depositSum]-[actualCostSum])/[exclusiveSum]*100,0) AS gpActual, IIf([exclusiveSum],([exclusiveSum]-[depositSum]-[listCostSum])/[exclusiveSum]*100,0) AS gpList, aStockGroup.StockGroup_Name AS department FROM StockList INNER JOIN (aStockItem INNER JOIN aStockGroup ON aStockItem.StockItem_StockGroupID = aStockGroup.StockGroupID) ON StockList.SaleItem_StockItemID = aStockItem.StockItemID ";
switch (this.cmbGroup.SelectedIndex) {
case 0:
sql = Strings.Replace(sql, "StockGroup", "PricingGroup");
break;
//Report.txtTitle.SetText "Product Performance - by Pricing Group"
case 1:
break;
//Report.txtTitle.SetText "Product Performance - by Stock Group"
case 2:
sql = Strings.Replace(sql, "StockGroup", "Supplier");
sql = Strings.Replace(sql, "aSupplier", "Supplier");
break;
//Report.txtTitle.SetText "Product Performance - by Supplier"
}
}
if (string.IsNullOrEmpty(rs.Fields("Link_SQL").Value)) {
} else {
sql = sql + rs.Fields("Link_SQL").Value;
}
sql = sql + lOrder;
string ptbl = null;
string t_day = null;
string t_Mon = null;
if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Day(DateAndTime.Today)))) == 1)
t_day = "0" + Strings.Trim(Convert.ToString(DateAndTime.Day(DateAndTime.Today)));
else
t_day = Convert.ToString(DateAndTime.Day(DateAndTime.Today));
if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Month(DateAndTime.Today)))) == 1)
t_Mon = "0" + Strings.Trim(Convert.ToString(DateAndTime.Month(DateAndTime.Today)));
else
t_Mon = Conversion.Str(DateAndTime.Month(DateAndTime.Today));
ptbl = modRecordSet.serverPath + "4POSProd" + Strings.Trim(Convert.ToString(DateAndTime.Year(DateAndTime.Today))) + Strings.Trim(t_Mon) + Strings.Trim(t_day);
if (fso.FileExists(ptbl + ".csv"))
fso.DeleteFile((ptbl + ".csv"));
FileSystem.FileOpen(1, ptbl + ".csv", OpenMode.Output);
//Open serverPath & "ProductPerformace.csv" For Output As #1
rs = modReport.getRSreport(ref sql);
//Write Out CSV
if (rs.BOF | rs.EOF) {
Interaction.MsgBox("There are no recods to export, Try Changing Day End date range", MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "Export Product Performance");
System.Windows.Forms.Cursor.Current = Cursors.Default;
return;
} else {
//First line as column headings
for (n = 0; n <= rs.Fields.Count - 1; n++) {
lne = lne + Strings.Chr(34) + rs.Fields(n).Name + Strings.Chr(34) + ",";
}
FileSystem.PrintLine(1, lne);
while (!rs.EOF) {
lne = "";
for (n = 0; n <= rs.Fields.Count - 1; n++) {
if (rs.Fields(n).Type == dbText) {
lne = lne + Strings.Chr(34) + rs.Fields(n).Value + Strings.Chr(34) + ",";
} else {
lne = lne + rs.Fields(n).Value + ",";
}
}
lne = Strings.Left(lne, Strings.Len(lne) - 1);
//get rid of last comma
FileSystem.PrintLine(1, lne);
rs.moveNext();
}
FileSystem.FileClose(1);
Interaction.MsgBox("Product performance CSV File, was successfully exported to : " + ptbl + ".csv");
}
System.Windows.Forms.Cursor.Current = Cursors.Default;
}
private bool getMasterDB()
{
bool functionReturnValue = false;
// ERROR: Not supported in C#: OnErrorStatement
//getMasterDB = True
cnnDBmaster = new ADODB.Connection();
string filename = null;
if (modWinVer.Win7Ver() == true) {
filename = "c:\\4posmaster\\4posmaster.mdb";
} else {
filename = "4posmaster.mdb";
}
if (System.IO.File.Exists(filename) == true) {
var _with1 = cnnDBmaster;
//.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0"
_with1.Provider = "Microsoft.ACE.OLEDB.12.0";
_with1.Open(filename);
//.Open()
functionReturnValue = true;
} else {
functionReturnValue = false;
}
//cnnDBmaster.Open("4posMaster")
//gMasterPath = Split(Split(cnnDBmaster.ConnectionString, ";DBQ=")(1), ";")(0)
//gMasterPath = Split(LCase(gMasterPath), "4posmaster.mdb")(0) '
//win 7
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
Scripting.TextStream textstream = default(Scripting.TextStream);
string lString = null;
if (modWinVer.Win7Ver() == true) {
//If fso.FileExists("C:\4POS\4POSWinPath.txt") Then
// Set textstream = fso.OpenTextFile("C:\4POS\4POSWinPath.txt", ForReading, True)
// lString = textstream.ReadAll
// serverPath = lString '& "pricing.mdb"
//Else
// serverPath = "C:\4POSServer\" '"pricing.mdb"
//End If
gMasterPath = "c:\\4posmaster\\";
return functionReturnValue;
//getMasterDB = True
}
//win 7
gMasterPath = Strings.Split(Strings.Split(cnnDBmaster.ConnectionString, ";DBQ=")[1], ";")[0];
gMasterPath = Strings.Split(Strings.LCase(gMasterPath), "4posmaster.mdb")[0];
return functionReturnValue;
openConnection_Error:
//
functionReturnValue = false;
return functionReturnValue;
}
private void doMode(ref short mode)
{
// Dim lNodeList As IXMLDOMNodeList
gMode = mode;
// Dim lNode As IXMLDOMNode
ADODB.Recordset rs = default(ADODB.Recordset);
short x = 0;
for (x = 0; x <= frmMode.Count - 1; x++) {
frmMode[x].Visible = false;
}
frmMode[gMode].Left = frmMode[0].Left;
frmMode[gMode].Top = frmMode[0].Top;
frmMode[gMode].Visible = true;
ADODB.Recordset rsME = default(ADODB.Recordset);
Scripting.TextStream textstreamME = default(Scripting.TextStream);
Scripting.FileSystemObject fsoME = new Scripting.FileSystemObject();
switch (gMode) {
case mdDayEnd:
rs = modRecordSet.getRS(ref "SELECT id FROM (SELECT COUNT(*) AS id FROM company INNER JOIN Sale ON Company.Company_DayEndID = Sale.Sale_DayEndID) transactions");
if (rs.Fields("id").Value) {
} else {
doMode(ref mdTransactions);
return;
}
this.cmdNext.Enabled = false;
break;
case mdTransactions:
rs = modRecordSet.getRS(ref "SELECT Count(Sale.SaleID) AS id FROM Company INNER JOIN ((Sale INNER JOIN DayEnd ON Sale.Sale_DayEndID = DayEnd.DayEndID) INNER JOIN MonthEnd ON DayEnd.DayEnd_MonthEndID = MonthEnd.MonthEndID) ON Company.Company_MonthEndID = MonthEnd.MonthEndID;");
if (rs.Fields("id").Value) {
doMode(ref mdConfirm);
return;
}
//If blMontheEnd = True Then
// str1 = "Month End Run failed to run, please try again!!!" & vbCrLf & "If the same problem persist, Consult 4POS for assistance"
// MsgBox str1, vbApplicationModal + vbInformation + vbOKOnly, "Month End"
//End If
this.cmdNext.Enabled = false;
break;
case mdConfirm:
break;
case mdComplete:
//Set lDOM = modGeneral.lsData.SQL("p4_monthEnd")
//modSecurity.checkSecurity
//do backup
rsME = modRecordSet.getRS(ref "select Company_BackupPath from Company");
if (rsME.RecordCount) {
if (!string.IsNullOrEmpty(rsME.Fields("Company_BackupPath").Value)) {
if (fsoME.FileExists("C:\\4POSBackup.txt"))
fsoME.DeleteFile("C:\\4POSBackup.txt", true);
textstreamME = fsoME.OpenTextFile("C:\\4POSBackup.txt", Scripting.IOMode.ForWriting, true);
textstreamME.Write(rsME.Fields("Company_BackupPath").Value);
textstreamME.Close();
Interaction.Shell("c:\\4pos\\4posbackup.exe", AppWinStyle.NormalFocus);
System.Windows.Forms.Application.DoEvents();
}
}
//do backup
this.cmdNext.Visible = false;
System.Windows.Forms.Application.DoEvents();
doMonthEnd();
break;
}
}
private bool ImportCSVHandMenu(ref string strFilePath)
{
bool functionReturnValue = false;
Scripting.FileSystemObject oFileSys = new Scripting.FileSystemObject();
Scripting.TextStream oFile = default(Scripting.TextStream);
string strCSV = null;
string strIn = null;
bool blEmpty = false;
string[] sSplit = null;
// ERROR: Not supported in C#: OnErrorStatement
blEmpty = true;
prgUpdate.Value = 1;
if (oFileSys.FileExists(strFilePath))
{
oFile = oFileSys.OpenTextFile(strFilePath, Scripting.IOMode.ForReading, false, Scripting.Tristate.TristateUseDefault);
System.Windows.Forms.Application.DoEvents();
while (!oFile.AtEndOfLine)
{
System.Windows.Forms.Application.DoEvents();
if (prgUpdate.Value == prgUpdate.Maximum)
{
System.Windows.Forms.Application.DoEvents();
prgUpdate.Value = 0;
}
else
{
prgUpdate.Value = prgUpdate.Value + 1;
System.Windows.Forms.Application.DoEvents();
}
blEmpty = false;
strCSV = oFile.ReadLine;
sSplit = Strings.Split(strCSV, "|");
System.Windows.Forms.Application.DoEvents();
if (strCSV != Constants.vbNullString)
{
System.Windows.Forms.Application.DoEvents();
sSplit[1] = Strings.Replace(sSplit[1], ",", "-");
sSplit[1] = Strings.Replace(sSplit[1], "'", "''");
modRecordSet.cnnDB.Execute("UPDATE Menu SET Menu_Update=True, Menu_Name = '" + sSplit[1] + "' WHERE ((MenuID)=" + sSplit[0] + ");");
System.Windows.Forms.Application.DoEvents();
}
}
if (blEmpty == true)
{
Interaction.MsgBox("Your CSV file is empty", MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Handheld StockTake");
functionReturnValue = false;
return(functionReturnValue);
}
functionReturnValue = true;
return(functionReturnValue);
}
else if (!oFileSys.FileExists(strFilePath))
{
Interaction.MsgBox("CSV File does not exist", MsgBoxStyle.Information, _4PosBackOffice.NET.My.MyProject.Application.Info.Title);
functionReturnValue = false;
return(functionReturnValue);
}
ImportError:
Interaction.MsgBox("Import aborted because " + Err().Description, MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Import Language");
return(functionReturnValue);
// ERROR: Not supported in C#: ResumeStatement
return(functionReturnValue);
}
public bool ImportCSVtoAccess(ref string strFilePath)
{
bool functionReturnValue = false;
Scripting.FileSystemObject oFileSys = new Scripting.FileSystemObject();
Scripting.TextStream oFile = default(Scripting.TextStream);
string strCSV = null;
string strFldName = null;
//String of fields' name
string strFV = null;
//String of fields' values
short iCount = 0;
short x = 0;
string strStr_1 = null;
string strStr_2 = null;
string strStr_3 = null;
string strStr_4 = null;
string temp = null;
string strIn = null;
bool blEmpty = false;
bool blTrue = false;
short cCounter = 0;
// ERROR: Not supported in C#: OnErrorStatement
System.Windows.Forms.Application.DoEvents();
System.Windows.Forms.Application.DoEvents();
cCounter = 0;
blTrue = false;
blEmpty = true;
prgUpdate.Value = 1;
dReceipt = false;
if (oFileSys.FileExists(strFilePath))
{
oFile = oFileSys.OpenTextFile(strFilePath, Scripting.IOMode.ForReading, false, Scripting.Tristate.TristateUseDefault);
while (!oFile.AtEndOfLine)
{
cCounter = cCounter + 1;
if (prgUpdate.Value == 300)
{
prgUpdate.Value = 0;
}
else
{
prgUpdate.Value = prgUpdate.Value + 0.1;
}
blEmpty = false;
strCSV = oFile.ReadLine;
strFV = strCSV;
//strFldName = "StockCode Text," & _
//' "combStockCode Text," & _
//' "comStockDescription TEXT(120)," & _
//' "Quantity Currency"
strFldName = "StockCode Text," + "combStockCode Text," + "Quantity Currency";
if (blTrue == false)
{
//strFldName = "StockCode Text,combStockCode Text,comStockDescription TEXT(120),Quantity Currency"
strFldName = "StockCode Text,combStockCode Text,Quantity Currency";
modRecordSet.cnnDB.Execute("CREATE TABLE RecieptEx (" + strFldName + ")");
blTrue = true;
dReceipt = true;
}
if (strCSV != Constants.vbNullString)
{
//Repeat 3 Times
//
strStr_1 = strCSV;
x = Strings.Len(strCSV) - Strings.Len(Strings.Right(strCSV, Strings.Len(strCSV) - Strings.InStr(strCSV, ",")));
strStr_1 = Strings.Mid(strStr_1, 1, x - 1);
strStr_2 = Strings.Right(strCSV, Strings.Len(strCSV) - Strings.InStr(strCSV, ","));
temp = strStr_2;
x = Strings.Len(strStr_2) - Strings.Len(Strings.Right(strStr_2, Strings.Len(strStr_2) - Strings.InStr(strStr_2, ",")));
strStr_2 = Strings.Mid(strStr_2, 1, x - 1);
strStr_3 = Strings.Right(temp, Strings.Len(temp) - Strings.InStr(temp, ","));
//temp = strStr_3
//x = Len(strStr_3) - Len(Right(strStr_3, Len(strStr_3) - InStr(strStr_3, ",")))
//strStr_3 = Mid$(strStr_3, 1, x - 1)
//strStr_4 = Right(temp, Len(temp) - InStr(temp, ","))
//strIn = "INSERT INTO RecieptEx (StockCode,combStockCode,comStockDescription,Quantity) VALUES " & _
//' "(" & Val(strStr_1) & "," & Val(strStr_2) & ",'" & Replace(Trim(strStr_3), "'", "''") & "'," & CCur(strStr_4) & ")"
strIn = "INSERT INTO RecieptEx (StockCode,combStockCode,Quantity) VALUES " + "(" + Conversion.Val(strStr_1) + "," + Conversion.Val(strStr_2) + "," + Convert.ToDecimal(strStr_3) + ")";
modRecordSet.cnnDB.Execute(strIn);
}
}
if (blEmpty == true)
{
Interaction.MsgBox("Your CSV file is empty", MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "4POS Bill of Material Import");
functionReturnValue = false;
return(functionReturnValue);
}
prgUpdate.Value = 300;
functionReturnValue = true;
return(functionReturnValue);
}
else if (!oFileSys.FileExists(strFilePath))
{
Interaction.MsgBox("CSV File does not exist", MsgBoxStyle.Information, "4POS Bill of Material Import");
functionReturnValue = false;
return(functionReturnValue);
}
ImportError:
if (cCounter == 0)
{
Interaction.MsgBox("Export Aborted Because " + Err().Description, MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "4POS Bill of Material Import");
}
else
{
Interaction.MsgBox("Export Aborted At Record Number " + cCounter + " [" + strStr_1 + "," + strStr_2 + "," + strStr_3 + "] " + Err().Description, MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "4POS Bill of Material Import");
}
if (dReceipt == true)
{
dooCleanUp();
}
return(functionReturnValue);
}
public void ExportToCSV(bool PrintHeader = true)
{
string ExportFilePath = null;
ADODB.Recordset rs = default(ADODB.Recordset);
int i = 0;
int TotalRecords = 0;
bool ErrorOccured = false;
short NumberOfFields = 0;
const string quote = "\"";
//Faster then Chr$(34)
string sql = null;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
cmdStart.Enabled = false;
cmdExit.Enabled = false;
txtPassword.Enabled = false;
string ptbl = null;
string t_day = null;
string t_Mon = null;
if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Day(DateAndTime.Today)))) == 1)
{
t_day = "0" + Strings.Trim(Convert.ToString(DateAndTime.Day(DateAndTime.Today)));
}
else
{
t_day = Convert.ToString(DateAndTime.Day(DateAndTime.Today));
}
if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Month(DateAndTime.Today)))) == 1)
{
t_Mon = "0" + Strings.Trim(Convert.ToString(DateAndTime.Month(DateAndTime.Today)));
}
else
{
t_Mon = Conversion.Str(DateAndTime.Month(DateAndTime.Today));
}
ExportFilePath = modRecordSet.serverPath + "4POSDebtor" + Strings.Trim(Convert.ToString(DateAndTime.Year(DateAndTime.Today))) + Strings.Trim(t_Mon) + Strings.Trim(t_day);
if (fso.FileExists(ExportFilePath + ".csv"))
{
fso.DeleteFile((ExportFilePath + ".csv"));
}
rs = modRecordSet.getRS(ref "SELECT CustomerID, Customer_InvoiceName, Customer_DepartmentName, Customer_FirstName, Customer_Surname, Customer_PhysicalAddress, Customer_PostalAddress, Customer_Telephone, Customer_Current as CurrentBalance,Customer_30Days as 30Days, Customer_60Days as 60days, Customer_90Days as 90Days, Customer_120Days as 120Days,Customer_150Days as 150Days FROM Customer");
prgBar.Maximum = rs.RecordCount;
if (rs.RecordCount > 0)
{
FileSystem.FileOpen(1, ExportFilePath + ".csv", OpenMode.Output);
var _with2 = modRecordSet.getRS(ref ref "SELECT CustomerID, Customer_InvoiceName, Customer_DepartmentName, Customer_FirstName, Customer_Surname, Customer_PhysicalAddress, Customer_PostalAddress, Customer_Telephone, Customer_Current as CurrentBalance,Customer_30Days as 30Days, Customer_60Days as 60days, Customer_90Days as 90Days, Customer_120Days as 120Days,Customer_150Days as 150Days FROM Customer");
rs.MoveFirst();
NumberOfFields = rs.Fields.Count - 1;
if (PrintHeader)
{
//Now add the field names
for (i = 0; i <= NumberOfFields - 1; i++)
{
FileSystem.Print(1, rs.Fields(i).name + ",");
//similar to the ones below
}
FileSystem.PrintLine(1, rs.Fields(NumberOfFields).name);
}
while (!rs.EOF)
{
prgBar.Value = prgBar.Value + 1;
// ERROR: Not supported in C#: OnErrorStatement
TotalRecords = TotalRecords + 1;
//If there is an emty field,
for (i = 0; i <= NumberOfFields; i++)
{
//add a , to indicate it is
if ((Information.IsDBNull(rs.Fields(i).Value)))
{
FileSystem.Print(1, ",");
//empty
}
else
{
if (i == NumberOfFields)
{
FileSystem.Print(1, quote + Strings.Trim(Convert.ToString(rs.Fields(i).Value)) + quote);
}
else
{
FileSystem.Print(1, quote + Strings.Trim(Convert.ToString(rs.Fields(i).Value)) + quote + ",");
}
}
//Putting data under "" will not
}
//confuse the reader of the file
DoEventsEx();
//between Dhaka, Bangladesh as two
FileSystem.PrintLine(1);
//fields or as one field.
rs.moveNext();
}
FileSystem.FileClose(1);
Interaction.MsgBox("Customer details were successfully exported to : " + FilePath + "" + "4POSProd" + Strings.Trim(Convert.ToString(DateAndTime.Year(DateAndTime.Today))) + Strings.Trim(t_Mon) + Strings.Trim(t_day) + ".csv", MsgBoxStyle.OkOnly, "Customers");
// DoEvents
// DoEvents
// MsgBox "Now Zeroising...", vbOKOnly, "Customers"
// cmdStart.Enabled = False
// cmdExit.Enabled = False
// Set rsZ = getRS("SELECT CustomerID FROM Customer")
// Do While Not rsZ.EOF
// DoEvents
// cmdProcess_Click (rsZ("CustomerID"))
// DoEvents
// rsZ.moveNext
// Loop
//MsgBox "Completed", vbOKOnly, "Customers"
//cmdStart.Enabled = True
//cmdExit.Enabled = True
this.Close();
}
System.Windows.Forms.Cursor.Current = Cursors.Default;
rs.Close();
//cnnDB.Close
//Set cnnDB = Nothing
//closeConnection
}
private void printBarcodePerson()
{
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
Scripting.TextStream lStream = default(Scripting.TextStream);
string lString = null;
string[] lArray = null;
string lText = null;
short x = 0;
short y = 0;
for (y = 0; y <= lstPerson.Items.Count - 1; y++) {
if (lstPerson.GetItemChecked(y)) {
rs.Filter = "PersonID = " + GID.GetItemData(ref lstPerson, ref y);
//rs.Filter = "PersonID = " & CLng(lstPerson(y))
lString = rs.Fields("label_textstream").Value;
lArray = Strings.Split(lString, Constants.vbCrLf);
lString = "";
for (x = 0; x <= Information.UBound(lArray); x++) {
lText = lArray[x];
lString = lString + Constants.vbCrLf + doString(ref lText, ref rs);
}
lStream = fso.OpenTextFile("c:\\aa.txt", Scripting.IOMode.ForWriting, true);
lStream.Write(lString);
lStream.Close();
lString = "C:\\AA.TXT";
modSpool.SpoolFile(lString, (lblPrinter.Text));
}
}
}
private void WriteFile(string pstrFilePathAndName, string pstrString)
{
Scripting.FileSystemObject fs = new Scripting.FileSystemObject();
Scripting.TextStream ts = null;
if (fs.FileExists(pstrFilePathAndName))
{
ts = fs.OpenTextFile(pstrFilePathAndName, Scripting.IOMode.ForWriting);
}
else
{
ts = fs.CreateTextFile(pstrFilePathAndName);
}
ts.Write(pstrString);
ts.Close();
}
public static bool openConnectionReport()
{
bool functionReturnValue = false;
string Path = null;
// ERROR: Not supported in C#: OnErrorStatement
bool createDayend = false;
string strDBPath = null;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
createDayend = false;
functionReturnValue = true;
cnnDBreport = new ADODB.Connection();
strDBPath = modRecordSet.serverPath + "report" + My.MyProject.Forms.frmMenu.lblUser.Tag + ".mdb";
var _with1 = cnnDBreport;
_with1.Provider = "Microsoft.ACE.OLEDB.12.0";
_with1.Properties("Jet OLEDB:System Database").Value = modRecordSet.serverPath + "Secured.mdw";
_with1.Open(strDBPath, "liquid", "lqd");
return functionReturnValue;
withPass:
//If serverPath = "" Then setNewServerPath
//If serverPath <> "" Then
cnnDBreport = new ADODB.Connection();
strDBPath = modRecordSet.serverPath + "report" + My.MyProject.Forms.frmMenu.lblUser.Tag + ".mdb";
//UPGRADE_WARNING: Couldn't resolve default property of object Path. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
Path = strDBPath + ";Jet " + "OLEDB:Database Password=lqd";
cnnDBreport.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
//UPGRADE_WARNING: Couldn't resolve default property of object Path. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
cnnDBreport.Open("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source= " + Path);
functionReturnValue = true;
return functionReturnValue;
openConnection_Error:
//End If
if (Err().Description == "[Microsoft][ODBC Microsoft Access Driver] Not a valid password.") {
goto withPass;
} else if (Err().Description == "Not a valid password.") {
goto withPass;
}
functionReturnValue = false;
Interaction.MsgBox(Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + " openConnectionReport object has not been made.");
return functionReturnValue;
}
public short ReadLine(ref string fname)
{
short functionReturnValue = 0;
Scripting.FileSystemObject oFSO = new Scripting.FileSystemObject();
Scripting.TextStream oFSTR = default(Scripting.TextStream);
int ret = 0;
int lCtr = 0;
if (oFSO.FileExists(fname)) {
oFSTR = oFSO.OpenTextFile(fname);
while (!oFSTR.AtEndOfStream) {
lCtr = lCtr + 1;
oFSTR.SkipLine();
}
functionReturnValue = lCtr;
oFSTR.Close();
oFSTR = null;
}
return functionReturnValue;
}
private bool AddDSN(string strDSN, string strDescription, ref string strDB, ref bool delete = false)
{
bool functionReturnValue = false;
//------------------------------------
//Usage:
// AddDSN "MyDSN", "This is a test", "C:\test\myDB.mdb"
//------------------------------------
// ERROR: Not supported in C#: OnErrorStatement
//win 7
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
Scripting.TextStream textstream = default(Scripting.TextStream);
string lString = null;
if (Win7Ver() == true) {
if (fso.FileExists("C:\\4POS\\4POSWinPath.txt"))
fso.DeleteFile("C:\\4POS\\4POSWinPath.txt", true);
lString = Strings.Replace(strDB, "\\pricing.mdb", "\\");
textstream = fso.OpenTextFile("C:\\4POS\\4POSWinPath.txt", Scripting.IOMode.ForWriting, true);
textstream.Write(lString);
textstream.Close();
}
//win 7
//Set the Driver Name
string strDriver = null;
string strFolder = null;
strFolder = strDB;
while (!(Strings.Right(strFolder, 1) == "\\")) {
strFolder = Strings.Left(strFolder, Strings.Len(strFolder) - 1);
}
strDriver = "Microsoft Access Driver (*.mdb)";
//Build the attributes - Attributes must be Null separated
string strAttributes = "";
strAttributes = strAttributes + "DESCRIPTION=" + strDescription + Strings.Chr(0);
strAttributes = strAttributes + "DSN=" + strDSN + Strings.Chr(0);
//strAttributes = strAttributes & "DATABASE=" & strDB & Chr(0)
strAttributes = strAttributes + "DBQ=" + strDB + Strings.Chr(0);
strAttributes = strAttributes + "systemDB=" + strFolder + "Secured.mdw" + Strings.Chr(0);
strAttributes = strAttributes + "UID=liquid" + Strings.Chr(0);
strAttributes = strAttributes + "PWD=lqd" + Strings.Chr(0);
//Create DSN
functionReturnValue = SQLConfigDataSource(0, ODBC_REMOVE_DSN, strDriver, strAttributes);
if (delete) {
} else {
functionReturnValue = SQLConfigDataSource(0, ODBC_ADD_DSN, strDriver, strAttributes);
}
functionReturnValue = true;
return functionReturnValue;
Hell:
functionReturnValue = false;
Interaction.MsgBox(Err().Description);
return functionReturnValue;
}
private bool ImportCSVHand(ref string strFilePath)
{
bool functionReturnValue = false;
Scripting.FileSystemObject oFileSys = new Scripting.FileSystemObject();
Scripting.TextStream oFile = default(Scripting.TextStream);
string strCSV = null;
string strFldName = null;
//String of fields' name
string strFV = null;
//String of fields' values
short iCount = 0;
short x = 0;
string strStr_1 = null;
string strStr_2 = null;
string Temp = null;
string strIn = null;
bool blEmpty = false;
bool blTrue = false;
string t_Day = null;
string t_Month = null;
t_Day = Strings.Trim(Convert.ToString(DateAndTime.Day(DateAndTime.Today)));
t_Month = Strings.Trim(Convert.ToString(DateAndTime.Month(DateAndTime.Today)));
System.Windows.Forms.Application.DoEvents();
System.Windows.Forms.Application.DoEvents();
ADODB.Recordset rs = default(ADODB.Recordset);
if (Strings.Len(t_Day) == 1)
t_Day = "0" + Strings.Trim(Convert.ToString(DateAndTime.Day(DateAndTime.Today)));
if (Strings.Len(t_Month) == 1)
t_Month = "0" + Strings.Trim(Convert.ToString(DateAndTime.Month(DateAndTime.Today)));
// ERROR: Not supported in C#: OnErrorStatement
//Create A Table Name
st_Name = "Handheld" + Strings.Trim(Convert.ToString(DateAndTime.Year(DateAndTime.Today))) + Strings.Trim(t_Month) + Strings.Trim(t_Day);
blTrue = false;
blEmpty = true;
dReceipt = false;
prgUpdate.Value = 1;
if (oFileSys.FileExists(strFilePath)) {
oFile = oFileSys.OpenTextFile(strFilePath, Scripting.IOMode.ForReading, false, Scripting.Tristate.TristateUseDefault);
System.Windows.Forms.Application.DoEvents();
while (!oFile.AtEndOfLine) {
System.Windows.Forms.Application.DoEvents();
if (prgUpdate.Value == prgUpdate.Maximum) {
System.Windows.Forms.Application.DoEvents();
prgUpdate.Value = 0;
} else {
prgUpdate.Value = prgUpdate.Value + 1;
System.Windows.Forms.Application.DoEvents();
}
blEmpty = false;
strCSV = oFile.ReadLine;
strFV = strCSV;
System.Windows.Forms.Application.DoEvents();
if (blTrue == false) {
strFldName = "HandHeldID Number,Handheld_Barcode Text(50), Quantity Currency";
System.Windows.Forms.Application.DoEvents();
modRecordSet.cnnDB.Execute("CREATE TABLE " + st_Name + " (" + strFldName + ")");
blTrue = true;
dReceipt = true;
}
if (strCSV != Constants.vbNullString) {
//Repeat 2 Times
//
strStr_1 = strCSV;
x = Strings.Len(strCSV) - Strings.Len(Strings.Right(strCSV, Strings.Len(strCSV) - Strings.InStr(strCSV, ",")));
strStr_1 = Strings.Mid(strStr_1, 1, x - 1);
System.Windows.Forms.Application.DoEvents();
strStr_2 = Strings.Right(strCSV, Strings.Len(strCSV) - Strings.InStr(strCSV, ","));
Temp = strStr_2;
System.Windows.Forms.Application.DoEvents();
rs = modRecordSet.getRS(ref "SELECT * FROM " + st_Name + " WHERE Handheld_Barcode = '" + Strings.Trim(strStr_1) + "'");
if (rs.RecordCount > 0) {
strIn = "UPDATE " + st_Name + " SET Quantity = " + rs.Fields("Quantity").Value + Conversion.Val(strStr_2) + " WHERE Handheld_Barcode = '" + Strings.Trim(strStr_1) + "'";
} else {
strIn = "INSERT INTO " + st_Name + " (Handheld_Barcode,Quantity) VALUES " + "('" + Strings.Trim(strStr_1) + "'," + Conversion.Val(strStr_2) + ")";
}
System.Windows.Forms.Application.DoEvents();
modRecordSet.cnnDB.Execute(strIn);
System.Windows.Forms.Application.DoEvents();
}
}
if (blEmpty == true) {
Interaction.MsgBox("Your CSV file is empty", MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Handheld StockTake");
functionReturnValue = false;
return functionReturnValue;
}
modRecordSet.cnnDB.Execute("INSERT INTO StockGroup (StockGroup_Name) VALUES ('" + st_Name + "')");
System.Windows.Forms.Application.DoEvents();
functionReturnValue = true;
return functionReturnValue;
} else if (!oFileSys.FileExists(strFilePath)) {
Interaction.MsgBox("CSV File does not exist", MsgBoxStyle.Information, _4PosBackOffice.NET.My.MyProject.Application.Info.Title);
functionReturnValue = false;
return functionReturnValue;
}
ImportError:
Interaction.MsgBox("Export aborted because " + Err().Description, MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Handheld StockTake");
if (dReceipt == true)
modRecordSet.cnnDB.Execute("DROP TABLE " + st_Name);
return functionReturnValue;
// ERROR: Not supported in C#: ResumeStatement
return functionReturnValue;
}
private void cmdOK_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
bool createDayend = false;
decimal x = default(decimal);
string revName = "";
//loading languages files
//Dim rs As Recordset
short tempLID = 0;
tempLID = 1;
rs = modRecordSet.getRS(ref "SELECT Company_LanguageID, Company_RightTL From Company");
if (rs.RecordCount) {
if (Information.IsDBNull(rs.Fields("Company_LanguageID").Value))
tempLID = 1;
if (rs.Fields("Company_LanguageID").Value == 0) {
tempLID = 1;
} else {
tempLID = rs.Fields("Company_LanguageID").Value;
}
if (Information.IsDBNull(rs.Fields("Company_RightTL").Value))
modRecordSet.iLangRTL = 0;
if (rs.Fields("Company_RightTL").Value == 0) {
modRecordSet.iLangRTL = 0;
} else {
modRecordSet.iLangRTL = rs.Fields("Company_RightTL").Value;
}
} else {
tempLID = 1;
modRecordSet.iLangRTL = 0;
}
modRecordSet.rsLang = modRecordSet.getRS(ref "SELECT * From LanguageLayoutLnk Where LanguageLayoutLnk_LanguageLayoutID = " + tempLID + ";");
//loading languages files
//loading help file
string helpPath = null;
//helpPath = App.Path & "\4POS4Dummies.chm"
//If Command = "1" Then helpPath = "C:\4POS\4POS4Dummies.chm"
helpPath = "C:\\4POS\\4POS4Dummies.chm";
if (fso.FileExists(helpPath)) {
modRecordSet.rsHelp = modRecordSet.getRS(ref "SELECT * From Help;");
} else {
modRecordSet.rsHelp = modRecordSet.getRS(ref "SELECT 0 AS Help_Section, 'A' AS Help_Form From Help;");
}
//loading help file
// ERROR: Not supported in C#: OnErrorStatement
rs = modRecordSet.getRS(ref "SELECT * FROM Person WHERE (Person_UserID = '" + Strings.Replace(this.txtUserName.Text, "'", "''") + "') AND (Person_Password = '******'", "''") + "')");
ADODB.Recordset rsRpt = default(ADODB.Recordset);
if (rs.BOF | rs.EOF) {
Interaction.MsgBox("Invalid User Name or Password, try again!", MsgBoxStyle.Exclamation, "Login");
txtPassword.Focus();
} else {
if (Convert.ToInt32(rs.Fields("Person_SecurityBit").Value + "0")) {
this.Close();
My.MyProject.Forms.frmMenu.lblUser.Text = rs.Fields("Person_FirstName").Value + " " + rs.Fields("Person_LastName").Value;
My.MyProject.Forms.frmMenu.lblUser.Tag = rs.Fields("PersonID").Value;
My.MyProject.Forms.frmMenu.gBit = rs.Fields("Person_SecurityBit").Value;
if (Strings.Len(rs.Fields("Person_QuickAccess").Value) > 0) {
for (x = Strings.Len(rs.Fields("Person_QuickAccess").Value); x >= 1; x += -1) {
revName = revName + Strings.Mid(rs.Fields("Person_QuickAccess").Value, x, 1);
}
if (Strings.LCase(Strings.Left(rs.Fields("Person_Position").Value, 8)) == "4posboss" & Strings.LCase(Strings.Right(rs.Fields("Person_Position").Value, Strings.Len(rs.Fields("Person_QuickAccess").Value))) == revName) {
My.MyProject.Forms.frmMenu.lblUser.ForeColor = System.Drawing.Color.Yellow;
My.MyProject.Forms.frmMenu.gSuper = true;
}
}
rsRpt = modRecordSet.getRS(ref "SELECT Company_LoadTRpt From Company");
if (Information.IsDBNull(rsRpt.Fields("Company_LoadTRpt").Value)) {
} else if (rsRpt.Fields("Company_LoadTRpt").Value == 0) {
} else {
if (fso.FileExists(modRecordSet.serverPath + "templateReport1.mdb")) {
if (fso.FileExists(modRecordSet.serverPath + "report" + My.MyProject.Forms.frmMenu.lblUser.Tag + ".mdb"))
fso.DeleteFile(modRecordSet.serverPath + "report" + My.MyProject.Forms.frmMenu.lblUser.Tag + ".mdb", true);
if (fso.FileExists(modRecordSet.serverPath + "templateReport.mdb"))
fso.DeleteFile(modRecordSet.serverPath + "templateReport.mdb", true);
fso.CopyFile(modRecordSet.serverPath + "templateReport1.mdb", modRecordSet.serverPath + "templateReport.mdb", true);
}
}
My.MyProject.Forms.frmMenu.loadMenu("stock");
if (fso.FileExists(modRecordSet.serverPath + "report" + My.MyProject.Forms.frmMenu.lblUser.Tag + ".mdb")) {
} else {
fso.CopyFile(modRecordSet.serverPath + "templateReport.mdb", modRecordSet.serverPath + "report" + My.MyProject.Forms.frmMenu.lblUser.Tag + ".mdb");
createDayend = true;
}
if (modReport.openConnectionReport()) {
} else {
Interaction.MsgBox("Unable to locate the Report Database!" + Constants.vbCrLf + Constants.vbCrLf + "The Update Controller wil terminate.", MsgBoxStyle.Critical, "SERVER ERROR");
System.Environment.Exit(0);
}
if (createDayend) {
// cnnDBreport.Execute "DELETE * FROM Report"
// cnnDBreport.Execute "INSERT INTO Report ( ReportID, Report_DayEndStartID, Report_DayEndEndID, Report_Heading ) SELECT 1 AS reportKey, Max(aDayEnd.DayEndID) AS MaxOfDayEndID, Max(aDayEnd.DayEndID) AS MaxOfDayEndID1, 'From ' & Format(Max([aDayEnd].[DayEnd_Date]),'ddd dd mmm yyyy') & ' to ' & Format(Max([aDayEnd].[DayEnd_Date]),'ddd dd mmm yyyy') & ' covering a dayend range of 1 days' AS theHeading FROM aDayEnd;"
// frmUpdateDayEnd.show 1
My.MyProject.Forms.frmMenu.cmdToday_Click(null, new System.EventArgs());
My.MyProject.Forms.frmMenu.cmdLoad_Click(null, new System.EventArgs());
}
rs = modReport.getRSreport(ref "SELECT DayEnd.DayEnd_Date AS fromDate, DayEnd_1.DayEnd_Date AS toDate FROM (Report INNER JOIN DayEnd ON Report.Report_DayEndStartID = DayEnd.DayEndID) INNER JOIN DayEnd AS DayEnd_1 ON Report.Report_DayEndEndID = DayEnd_1.DayEndID;");
if (rs.RecordCount) {
My.MyProject.Forms.frmMenu._DTPicker1_0.Value = Strings.Format(rs.Fields("fromDate").Value, "yyyy");
My.MyProject.Forms.frmMenu._DTPicker1_0.Value = Strings.Format(rs.Fields("fromDate").Value, "mm");
My.MyProject.Forms.frmMenu._DTPicker1_0.Value = Strings.Format(rs.Fields("fromDate").Value, "dd");
My.MyProject.Forms.frmMenu._DTPicker1_1.Value = Strings.Format(rs.Fields("toDate").Value, "yyyy");
My.MyProject.Forms.frmMenu._DTPicker1_1.Value = Strings.Format(rs.Fields("toDate").Value, "mm");
My.MyProject.Forms.frmMenu._DTPicker1_1.Value = Strings.Format(rs.Fields("toDate").Value, "dd");
}
My.MyProject.Forms.frmMenu.setDayEndRange();
My.MyProject.Forms.frmMenu.lblDayEndCurrent.Text = My.MyProject.Forms.frmMenu.lblDayEnd.Text;
} else {
Interaction.MsgBox("You do not have the correct permissions to access the 4POS Office Application, try again!", MsgBoxStyle.Exclamation, "Login");
this.txtUserName.Focus();
System.Windows.Forms.SendKeys.Send("{Home}+{End}");
}
}
}
public bool ImportCSVtoAccess(ref string strFilePath)
{
bool functionReturnValue = false;
Scripting.FileSystemObject oFileSys = new Scripting.FileSystemObject();
Scripting.TextStream oFile = default(Scripting.TextStream);
string strCSV = null;
string strFldName = null;
//String of fields' name
string strFV = null;
//String of fields' values
short iCount = 0;
short x = 0;
string strStr_1 = null;
string strStr_2 = null;
string strStr_3 = null;
string strStr_4 = null;
string Temp = null;
string strIn = null;
bool blEmpty = false;
bool blTrue = false;
short cCounter = 0;
// ERROR: Not supported in C#: OnErrorStatement
System.Windows.Forms.Application.DoEvents();
System.Windows.Forms.Application.DoEvents();
cCounter = 0;
blTrue = false;
blEmpty = true;
prgUpdate.Value = 1;
prgUpdate.Maximum = 300;
dReceipt = false;
if (oFileSys.FileExists(strFilePath)) {
oFile = oFileSys.OpenTextFile(strFilePath, Scripting.IOMode.ForReading, false, Scripting.Tristate.TristateUseDefault);
while (!oFile.AtEndOfLine) {
cCounter = cCounter + 1;
// If prgUpdate.Value = 300 Then
// prgUpdate.Value = 0
// Else
// prgUpdate.Value = prgUpdate.Value + 1
// End If
if (prgUpdate.Value == prgUpdate.Maximum) {
System.Windows.Forms.Application.DoEvents();
prgUpdate.Value = 0;
} else {
prgUpdate.Value = prgUpdate.Value + 1;
System.Windows.Forms.Application.DoEvents();
}
blEmpty = false;
strCSV = oFile.ReadLine;
strFV = strCSV;
//strFldName = "StockCode Text," & _
//' "combStockCode Text," & _
//' "comStockDescription TEXT(120)," & _
//' "Quantity Currency"
strFldName = "StockCode Text," + "combStockCode Text," + "Quantity Currency";
if (blTrue == false) {
//strFldName = "StockCode Text,combStockCode Text,comStockDescription TEXT(120),Quantity Currency"
strFldName = "StockCode Text,combStockCode Text,Quantity Currency";
modRecordSet.cnnDB.Execute("CREATE TABLE RecieptEx (" + strFldName + ")");
blTrue = true;
dReceipt = true;
}
if (strCSV != Constants.vbNullString) {
//Repeat 3 Times
//
strStr_1 = strCSV;
x = Strings.Len(strCSV) - Strings.Len(Strings.Right(strCSV, Strings.Len(strCSV) - Strings.InStr(strCSV, ",")));
strStr_1 = Strings.Mid(strStr_1, 1, x - 1);
strStr_2 = Strings.Right(strCSV, Strings.Len(strCSV) - Strings.InStr(strCSV, ","));
Temp = strStr_2;
x = Strings.Len(strStr_2) - Strings.Len(Strings.Right(strStr_2, Strings.Len(strStr_2) - Strings.InStr(strStr_2, ",")));
strStr_2 = Strings.Mid(strStr_2, 1, x - 1);
strStr_3 = Strings.Right(Temp, Strings.Len(Temp) - Strings.InStr(Temp, ","));
//temp = strStr_3
//x = Len(strStr_3) - Len(Right(strStr_3, Len(strStr_3) - InStr(strStr_3, ",")))
//strStr_3 = Mid$(strStr_3, 1, x - 1)
//strStr_4 = Right(temp, Len(temp) - InStr(temp, ","))
//strIn = "INSERT INTO RecieptEx (StockCode,combStockCode,comStockDescription,Quantity) VALUES " & _
//' "(" & Val(strStr_1) & "," & Val(strStr_2) & ",'" & Replace(Trim(strStr_3), "'", "''") & "'," & CCur(strStr_4) & ")"
strIn = "INSERT INTO RecieptEx (StockCode,combStockCode,Quantity) VALUES " + "(" + Conversion.Val(strStr_1) + "," + Conversion.Val(strStr_2) + "," + Convert.ToDecimal(strStr_3) + ")";
modRecordSet.cnnDB.Execute(strIn);
}
}
if (blEmpty == true) {
Interaction.MsgBox("Your CSV file is empty", MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "4POS Bill of Material Import");
functionReturnValue = false;
return functionReturnValue;
}
prgUpdate.Value = 300;
functionReturnValue = true;
return functionReturnValue;
} else if (!oFileSys.FileExists(strFilePath)) {
Interaction.MsgBox("CSV File does not exist", MsgBoxStyle.Information, "4POS Bill of Material Import");
functionReturnValue = false;
return functionReturnValue;
}
ImportError:
if (cCounter == 0) {
Interaction.MsgBox("Export Aborted Because " + Err().Description, MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "4POS Bill of Material Import");
} else {
Interaction.MsgBox("Export Aborted At Record Number " + cCounter + " [" + strStr_1 + "," + strStr_2 + "," + strStr_3 + "] " + Err().Description, MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "4POS Bill of Material Import");
}
//Resume Next
if (dReceipt == true)
dooCleanUp();
return functionReturnValue;
}
private void frmMaster_Load(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
string lFile = null;
Label1.AddRange(new Label[] {
_Label1_0,
_Label1_1,
_Label1_2,
_Label1_3,
_Label1_4
});
//ResizeForm(Me, pixelToTwips(Me.Width, True), pixelToTwips(Me.Height, False), 0)
ExecCmd(ref "cmd /C DEL c:\\*.tmp");
if (fso.FileExists("C:\\4POS\\pos\\data.ug")) {
if (fso.FileExists("C:\\4POS\\pos\\4POSupgrade.exe")) {
Interaction.Shell("C:\\4POS\\pos\\4POSupgrade.exe", AppWinStyle.NormalFocus);
} else {
Interaction.MsgBox("An upgrade is required, but the upgrade unility can not be located!" + Constants.vbCrLf + Constants.vbCrLf + "Please contact a 4POS representative to assist you in resolving this problem.", MsgBoxStyle.Critical, "UPGRADE");
}
System.Environment.Exit(0);
}
modUtilities.setShortDateFormat();
short tempLID = 0;
string helpPath = null;
Scripting.TextStream DayEndtextstream = default(Scripting.TextStream);
string lString = null;
if (getMasterDB()) {
//loadLanguage
loadCompanies();
} else {
if (modRecordSet.openConnection()) {
} else {
if (fso.FileExists("c:\\4posServer\\pricing.mdb")) {
lFile = "c:\\4posServer\\pricing.mdb";
} else {
this.CDmasterOpen.Title = "Select the path to the application database ...";
//UPGRADE_WARNING: Filter has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="9B7D5ADD-D8FE-4819-A36C-6DEDAF088CC7"'
CDmasterOpen.Filter = "Application Data Base|pricing.mdb";
CDmasterOpen.FileName = "";
this.CDmasterOpen.ShowDialog();
lFile = CDmasterOpen.FileName;
}
if (string.IsNullOrEmpty(lFile)) {
System.Environment.Exit(0);
} else {
if (AddDSN("4POS", "4POS Pricing File", lFile)) {
this.Close();
Interaction.MsgBox("The new data source has just been configured into the application suite." + Constants.vbCrLf + Constants.vbCrLf + "Please restart this application", MsgBoxStyle.Information, "DATA SOURCE");
System.Environment.Exit(0);
} else {
System.Environment.Exit(0);
}
}
}
//loading languages files
//Dim rs As Recordset
tempLID = 1;
rs = modRecordSet.getRS(ref "SELECT Company_LanguageID, Company_RightTL From Company");
if (rs.RecordCount) {
if (Information.IsDBNull(rs.Fields("Company_LanguageID").Value))
tempLID = 1;
if (rs.Fields("Company_LanguageID").Value == 0) {
tempLID = 1;
} else {
tempLID = rs.Fields("Company_LanguageID").Value;
}
if (Information.IsDBNull(rs.Fields("Company_RightTL").Value))
modRecordSet.iLangRTL = 0;
if (rs.Fields("Company_RightTL").Value == 0) {
modRecordSet.iLangRTL = 0;
} else {
modRecordSet.iLangRTL = rs.Fields("Company_RightTL").Value;
}
} else {
tempLID = 1;
modRecordSet.iLangRTL = 0;
}
modRecordSet.rsLang = modRecordSet.getRS(ref "SELECT * From LanguageLayoutLnk Where LanguageLayoutLnk_LanguageLayoutID = " + tempLID + ";");
//loading languages files
//loading help file
//helpPath = App.Path & "\4POS4Dummies.chm"
//If Command = "1" Then helpPath = "C:\4POS\4POS4Dummies.chm"
helpPath = "C:\\4POS\\4POS4Dummies.chm";
if (fso.FileExists(helpPath)) {
modRecordSet.rsHelp = modRecordSet.getRS(ref "SELECT * From Help;");
} else {
modRecordSet.rsHelp = modRecordSet.getRS(ref "SELECT 0 AS Help_Section, 'A' AS Help_Form From Help;");
}
//loading help file
//Dim lTextstream As textstream
lString = modRecordSet.serverPath + "data\\4POSInterface\\4POSAUTODE.txt";
if (fso.FileExists(lString)) {
DayEndtextstream = fso.OpenTextFile(lString, Scripting.IOMode.ForReading, true);
lString = DayEndtextstream.ReadAll;
DayEndtextstream.Close();
DayEndtextstream = null;
if (Information.IsDate(lString)) {
modApplication.bolAutoDE = true;
modApplication.dateDayEnd = Convert.ToDateTime(lString);
modApplication.doMenuForms(ref "frmdayend");
System.Windows.Forms.Application.DoEvents();
} else {
Interaction.MsgBox("Auto DayEnd file was detected but file is invalid.");
System.Environment.Exit(0);
}
} else {
loadLanguage();
frmLogin f = new frmLogin();
f.Show();
//frmLogin.Show()
}
//Me.Close()
this.Hide();
}
}
private void cmdLoad_Click(System.Object eventSender, System.EventArgs eventArgs)
{
string dbText = null;
string sql = null;
string stFileName = null;
string lOrder = null;
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsData = default(ADODB.Recordset);
//Exporting file...
string lne = null;
short n = 0;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
switch (this.cmbSortField.SelectedIndex)
{
case 0:
lOrder = "StockItem_Name";
break;
case 1:
lOrder = "[exclusiveSum]-[depositSum]-[listCostSum]";
break;
case 2:
lOrder = "[exclusiveSum]-[depositSum]";
break;
case 3:
lOrder = "[exclusiveSum]-[depositSum]-[listCostSum]";
break;
case 4:
lOrder = "IIf([exclusiveSum]=0,0,([exclusiveSum]-[depositSum]-[listCostSum])/[exclusiveSum])";
break;
case 5:
lOrder = "StockList.quantitySum";
break;
}
if (this.cmbSort.SelectedIndex)
{
lOrder = lOrder + " DESC";
}
lOrder = " ORDER BY " + lOrder;
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modReport.getRSreport(ref "SELECT * FROM Link Where LinkID=2 AND Link_SectionID=1");
if (_optType_0.Checked)
{
sql = "SELECT aStockItem.StockItemID, aStockItem.StockItem_Name, StockList.inclusiveSum AS inclusive, StockList.exclusiveSum AS exclusive, [exclusiveSum]-[depositSum] AS price, [exclusiveSum]-[depositSum] AS content, [exclusiveSum]-[depositSum]-[listCostSum] AS listProfit, [exclusiveSum]-[depositSum]-[actualCostSum] AS actualProfit, StockList.quantitySum AS quantity, StockList.listCostSum AS listCost, StockList.actualCostSum AS actualCost, IIf([exclusiveSum],([exclusiveSum]-[depositSum]-[actualCostSum])/[exclusiveSum]*100,0) AS gpActual, IIf([exclusiveSum],([exclusiveSum]-[depositSum]-[listCostSum])/[exclusiveSum]*100,0) AS gpList, aStockGroup.StockGroup_Name AS department FROM StockList INNER JOIN (aStockItem INNER JOIN aStockGroup ON aStockItem.StockItem_StockGroupID = aStockGroup.StockGroupID) ON StockList.SaleItem_StockItemID = aStockItem.StockItemID ";
}
else
{
sql = "SELECT aStockItem.StockItemID, aStockItem.StockItem_Name, StockList.inclusiveSum AS inclusive, StockList.exclusiveSum AS exclusive, [exclusiveSum]-[depositSum] AS price, [exclusiveSum]-[depositSum] AS content, [exclusiveSum]-[depositSum]-[listCostSum] AS listProfit, [exclusiveSum]-[depositSum]-[actualCostSum] AS actualProfit, StockList.quantitySum AS quantity, StockList.listCostSum AS listCost, StockList.actualCostSum AS actualCost, IIf([exclusiveSum],([exclusiveSum]-[depositSum]-[actualCostSum])/[exclusiveSum]*100,0) AS gpActual, IIf([exclusiveSum],([exclusiveSum]-[depositSum]-[listCostSum])/[exclusiveSum]*100,0) AS gpList, aStockGroup.StockGroup_Name AS department FROM StockList INNER JOIN (aStockItem INNER JOIN aStockGroup ON aStockItem.StockItem_StockGroupID = aStockGroup.StockGroupID) ON StockList.SaleItem_StockItemID = aStockItem.StockItemID ";
switch (this.cmbGroup.SelectedIndex)
{
case 0:
sql = Strings.Replace(sql, "StockGroup", "PricingGroup");
break;
//Report.txtTitle.SetText "Product Performance - by Pricing Group"
case 1:
break;
//Report.txtTitle.SetText "Product Performance - by Stock Group"
case 2:
sql = Strings.Replace(sql, "StockGroup", "Supplier");
sql = Strings.Replace(sql, "aSupplier", "Supplier");
break;
//Report.txtTitle.SetText "Product Performance - by Supplier"
}
}
if (string.IsNullOrEmpty(rs.Fields("Link_SQL").Value))
{
}
else
{
sql = sql + rs.Fields("Link_SQL").Value;
}
sql = sql + lOrder;
string ptbl = null;
string t_day = null;
string t_Mon = null;
if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Day(DateAndTime.Today)))) == 1)
{
t_day = "0" + Strings.Trim(Convert.ToString(DateAndTime.Day(DateAndTime.Today)));
}
else
{
t_day = Convert.ToString(DateAndTime.Day(DateAndTime.Today));
}
if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Month(DateAndTime.Today)))) == 1)
{
t_Mon = "0" + Strings.Trim(Convert.ToString(DateAndTime.Month(DateAndTime.Today)));
}
else
{
t_Mon = Conversion.Str(DateAndTime.Month(DateAndTime.Today));
}
ptbl = modRecordSet.serverPath + "4POSProd" + Strings.Trim(Convert.ToString(DateAndTime.Year(DateAndTime.Today))) + Strings.Trim(t_Mon) + Strings.Trim(t_day);
if (fso.FileExists(ptbl + ".csv"))
{
fso.DeleteFile((ptbl + ".csv"));
}
FileSystem.FileOpen(1, ptbl + ".csv", OpenMode.Output);
//Open serverPath & "ProductPerformace.csv" For Output As #1
rs = modReport.getRSreport(ref sql);
//Write Out CSV
if (rs.BOF | rs.EOF)
{
Interaction.MsgBox("There are no recods to export, Try Changing Day End date range", MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "Export Product Performance");
System.Windows.Forms.Cursor.Current = Cursors.Default;
return;
}
else
{
//First line as column headings
for (n = 0; n <= rs.Fields.Count - 1; n++)
{
lne = lne + Strings.Chr(34) + rs.Fields(n).Name + Strings.Chr(34) + ",";
}
FileSystem.PrintLine(1, lne);
while (!rs.EOF)
{
lne = "";
for (n = 0; n <= rs.Fields.Count - 1; n++)
{
if (rs.Fields(n).Type == dbText)
{
lne = lne + Strings.Chr(34) + rs.Fields(n).Value + Strings.Chr(34) + ",";
}
else
{
lne = lne + rs.Fields(n).Value + ",";
}
}
lne = Strings.Left(lne, Strings.Len(lne) - 1);
//get rid of last comma
FileSystem.PrintLine(1, lne);
rs.moveNext();
}
FileSystem.FileClose(1);
Interaction.MsgBox("Product performance CSV File, was successfully exported to : " + ptbl + ".csv");
}
System.Windows.Forms.Cursor.Current = Cursors.Default;
}
private string getSerialNumber()
{
string functionReturnValue = null;
Scripting.FileSystemObject fso = new Scripting.FileSystemObject();
Scripting.Folder fsoFolder = default(Scripting.Folder);
functionReturnValue = "";
if (fso.FolderExists(modRecordSet.serverPath)) {
fsoFolder = fso.GetFolder(modRecordSet.serverPath);
functionReturnValue = Convert.ToString(fsoFolder.Drive.SerialNumber);
}
return functionReturnValue;
}
private bool ImportCSVHand(ref string strFilePath)
{
bool functionReturnValue = false;
string sql = null;
Scripting.FileSystemObject oFileSys = new Scripting.FileSystemObject();
Scripting.TextStream oFile = default(Scripting.TextStream);
string strCSV = null;
string strIn = null;
bool blEmpty = false;
string[] sSplit = null;
// ERROR: Not supported in C#: OnErrorStatement
blEmpty = true;
prgUpdate.Value = 1;
ADODB.Recordset rsLang = default(ADODB.Recordset);
if (oFileSys.FileExists(strFilePath))
{
modRecordSet.cnnDB.Execute("INSERT INTO LanguageLayout ( LanguageLayout_Name ) SELECT 'New Language';");
rsLang = modRecordSet.getRS(ref "SELECT Max(LanguageLayout.LanguageLayoutID) AS MaxOfLanguageLayoutID FROM LanguageLayout;");
modRecordSet.cnnDB.Execute("INSERT INTO LanguageLayoutLnk ( LanguageLayoutLnk_LanguageID, LanguageLayoutLnk_LanguageLayoutID, LanguageLayoutLnk_Description, LanguageLayoutLnk_RightTL, LanguageLayoutLnk_Screen ) SELECT theJoin.LanguageID, theJoin.LanguageLayoutID, 'None' AS Expr1, 0 AS Expr2, 'None' AS Expr3 FROM (SELECT Language.LanguageID, LanguageLayout.LanguageLayoutID From Language, LanguageLayout WHERE (((LanguageLayout.LanguageLayoutID)=" + rsLang.Fields("MaxOfLanguageLayoutID").Value + "))) AS theJoin LEFT JOIN LanguageLayoutLnk ON (theJoin.LanguageID = LanguageLayoutLnk.LanguageLayoutLnk_LanguageID) AND (theJoin.LanguageLayoutID = LanguageLayoutLnk.LanguageLayoutLnk_LanguageLayoutID) WHERE (((LanguageLayoutLnk.LanguageLayoutLnk_LanguageID) Is Null));");
sql = "UPDATE LanguageLayoutLnk AS LanguageLayoutLnk_1 INNER JOIN LanguageLayoutLnk ON LanguageLayoutLnk_1.LanguageLayoutLnk_LanguageID = LanguageLayoutLnk.LanguageLayoutLnk_LanguageID SET LanguageLayoutLnk.LanguageLayoutLnk_Description = [LanguageLayoutLnk_1]![LanguageLayoutLnk_Description], LanguageLayoutLnk.LanguageLayoutLnk_RightTL = [LanguageLayoutLnk_1]![LanguageLayoutLnk_RightTL], LanguageLayoutLnk.LanguageLayoutLnk_Section = [LanguageLayoutLnk_1]![LanguageLayoutLnk_Section], LanguageLayoutLnk.LanguageLayoutLnk_Screen = [LanguageLayoutLnk_1]![LanguageLayoutLnk_Screen] ";
sql = sql + "WHERE (((LanguageLayoutLnk.LanguageLayoutLnk_Description)='None') AND ((LanguageLayoutLnk.LanguageLayoutLnk_Screen)='None') AND ((LanguageLayoutLnk.LanguageLayoutLnk_LanguageLayoutID)=" + rsLang.Fields("MaxOfLanguageLayoutID").Value + ") AND ((LanguageLayoutLnk_1.LanguageLayoutLnk_LanguageLayoutID)=1));";
modRecordSet.cnnDB.Execute(sql);
Command3.Tag = rsLang.Fields("MaxOfLanguageLayoutID").Value;
oFile = oFileSys.OpenTextFile(strFilePath, Scripting.IOMode.ForReading, false, Scripting.Tristate.TristateUseDefault);
System.Windows.Forms.Application.DoEvents();
while (!oFile.AtEndOfLine)
{
System.Windows.Forms.Application.DoEvents();
if (prgUpdate.Value == prgUpdate.Maximum)
{
System.Windows.Forms.Application.DoEvents();
prgUpdate.Value = 0;
}
else
{
prgUpdate.Value = prgUpdate.Value + 1;
System.Windows.Forms.Application.DoEvents();
}
blEmpty = false;
strCSV = oFile.ReadLine;
sSplit = Strings.Split(strCSV, "|");
System.Windows.Forms.Application.DoEvents();
if (strCSV != Constants.vbNullString)
{
System.Windows.Forms.Application.DoEvents();
sSplit[1] = Strings.Replace(sSplit[1], ",", "-");
sSplit[1] = Strings.Replace(sSplit[1], "'", "''");
strIn = "UPDATE LanguageLayoutLnk SET LanguageLayoutLnk_Description = '" + sSplit[1] + "' WHERE LanguageLayoutLnk_LanguageID = " + sSplit[0] + " AND LanguageLayoutLnk_LanguageLayoutID = " + rsLang.Fields("MaxOfLanguageLayoutID").Value + ";";
modRecordSet.cnnDB.Execute(strIn);
System.Windows.Forms.Application.DoEvents();
}
}
if (blEmpty == true)
{
Interaction.MsgBox("Your CSV file is empty", MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Handheld StockTake");
functionReturnValue = false;
return(functionReturnValue);
}
functionReturnValue = true;
return(functionReturnValue);
}
else if (!oFileSys.FileExists(strFilePath))
{
Interaction.MsgBox("CSV File does not exist", MsgBoxStyle.Information, _4PosBackOffice.NET.My.MyProject.Application.Info.Title);
functionReturnValue = false;
return(functionReturnValue);
}
ImportError:
Interaction.MsgBox("Import aborted because " + Err().Description, MsgBoxStyle.ApplicationModal + MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Import Language");
return(functionReturnValue);
// ERROR: Not supported in C#: ResumeStatement
return(functionReturnValue);
}
