public object[] db_access(string strSQL) { ADODB.Connection objCon; ADODB.Recordset objRec; object[,] dataRows; object[] dataSuite; string strCon; objCon = new ADODB.Connection(); objRec = new ADODB.Recordset(); //establish the connection string and open the database connection strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" + "database=functional_test_data;option=3"; objCon.Open(strCon); //execute the SQL and return the recrodset of results objRec = objCon.Execute(strSQL, out missing, 0); //populate a two dinmensional object array with the results dataRows = objRec.GetRows(); //get a one dimensional array that can be placed into the Test Suite dropdown dataSuite = thinArray(dataRows); //close the recordset objRec.Close(); //close the database connection objCon.Close(); return dataSuite; }
private void beginUpdate() { picInner.Width = 0; gCNT = 0; System.Windows.Forms.Application.DoEvents(); //fill the templateReport with data ADODB.Connection ccndbReport = default(ADODB.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 ccndbReport = modRecordSet.openConnectionInstance(ref "templatereport.mdb"); for (Y = 0; Y <= Information.UBound(lTable); Y++) { moveItem(); System.Windows.Forms.Application.DoEvents(); ccndbReport.Execute("DELETE * FROM " + lTable[Y] + ";"); ccndbReport.Execute("INSERT INTO " + lTable[Y] + " SELECT * FROM " + lTable[Y] + "1;"); } //fill the templateReport with data System.Windows.Forms.Application.DoEvents(); this.Close(); }
private void cmdCompany_Click(System.Object eventSender, System.EventArgs eventArgs) { cnnDBmaster.Execute("UPDATE locationCompany SET locationCompany.locationCompany_Name = '" + Strings.Replace(txtCompany.Text, "'", "''") + "' WHERE (((locationCompany.locationCompanyID)=" + lblCompany.Tag + "));"); loadCompanies(); lvLocation.FocusedItem = lvLocation.Items["k" + lblCompany.Tag]; lblCompany.Text = lvLocation.FocusedItem.Text + " - " + lvLocation.FocusedItem.SubItems[1].Text; }
//----------------------------------------------------------------------------------------------------------------------- // 설명 : 쿼리 실행 함수 // // 결과 : 쿼리 실행 //----------------------------------------------------------------------------------------------------------------------- public static void ExecCN(string sql, bool OpenCloseCN) { try { //ADO 연결 if (OpenCloseCN) { OpenCN(false); } ////쿼리 실행 object null_object = null; //CN.Execute(sql, out null_object, -1); CN.Execute(sql, out null_object); //ADO 연결 해제 if (OpenCloseCN) { CloseAll(); } } catch (Exception) { //MessageBox.Show(e.Message + "\n Data Base Error!", "확인"); CloseCN(0); } finally { } }
public static void SetDbStampADOX(string fileName, int Dictionary_id) { Catalog cat = new Catalog(); object ret; ADODB.Connection conn = new ADODB.Connection(); conn.ConnectionString = CreateMdbConnectionString(fileName); conn.Open(); cat.ActiveConnection = conn; conn.Execute(@"CREATE TABLE [_DbStamp] ([Key] TEXT(255) PRIMARY KEY, [Val] TEXT(255));", out ret, 0); conn.Execute($"INSERT INTO [_DbStamp] ([Key], [Val]) VALUES ('Dictionary_id', '{Dictionary_id}');", out ret, 0); Table tbl = cat.Tables["_DbStamp"]; tbl.Properties["Jet OLEDB:Table Hidden In Access"].Value = true; conn.Close(); }
public bool Execute(string sql) { bool stat = false; object obj; try { if (Connected() == true) { if (cnn == null) { cnn = new ADODB.Connection(); } if (cnn.State == 0) { cnn.Open(cnn_str, "", "", 0); } if (cnn.State == 1) { cnn.Execute(sql, out obj, 0); cnn.Close(); stat = true; } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } return(stat); }
private void ExecuteQuery() { object recs; connection.Execute(queryBuilder.ToString(), out recs); queryBuilder.Remove(0, queryBuilder.Length); }
/// <summary> /// /// </summary> /// <param name="conn"></param> /// <param name="sql"></param> /// <returns></returns> public object ExecSql(ref ADODB.Connection conn, string sql) { object recordsAffected; conn.Execute(sql, out recordsAffected, -1); return(recordsAffected); }
public object[] db_access(string strSQL) { ADODB.Connection objCon; ADODB.Recordset objRec; object[,] dataRows; object[] dataSuite; string strCon; objCon = new ADODB.Connection(); objRec = new ADODB.Recordset(); //establish the connection string and open the database connection strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" + "database=functional_test_data;option=3"; objCon.Open(strCon); //execute the SQL and return the recrodset of results objRec = objCon.Execute(strSQL, out missing, 0); //populate a two dinmensional object array with the results dataRows = objRec.GetRows(); //get a one dimensional array that can be placed into the Test Suite dropdown dataSuite = thinArray(dataRows); //close the recordset objRec.Close(); //close the database connection objCon.Close(); return(dataSuite); }
public static void ReadDB(string tableName, string fileName) { string filePath = @"D:\xl2cad.mdb"; if (File.Exists(filePath)) { //select * into 建立 新的表。 //[[Excel 8.0;database= excel名].[sheet名] 如果是新建sheet表不能加$,如果向sheet里插入数据要加$. //sheet最多存储65535条数据。 ADODB.Connection cn = null; try { object ra = null; string sql = "select top 65535 * into [Excel 8.0;database=" + fileName + "].[" + tableName + "] from " + tableName; cn = new ADODB.Connection(); cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1); cn.Execute(sql, out ra, -1); cn.Close(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cn); } catch (System.Exception ex) { MessageBox.Show(ex.ToString()); } finally { cn.Close(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cn); System.GC.Collect(); } } }
//修改数据库密码 public void editDBPwd_B(string old_password, string new_password) { try { string fileName = Application.StartupPath + @"\source\StuContact.mdb";//数据库路径 string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Jet OLEDB:Database password="******"ALTER DATABASE PASSWORD " + new_password + " " + old_password; ADODB.Connection cn = new ADODB.Connection(); cn.Mode = ADODB.ConnectModeEnum.adModeShareExclusive;//以独占模式打开数据库,不然不能修改密码 cn.Open(conn, null, null, -1); // 执行 SQL 语句以更改密码 object num; cn.Execute(sql, out num, -1); cn.Close(); //开始修改配置文件 config.writeConfig_IsEditDBandAddPwd(true, new_password); MessageBox.Show("密码修改成功,请牢记您的密码", "修改提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("数据库密码未修改,因为:" + ex.Message, "提示", MessageBoxButtons.OK); } }
/// <summary> /// 删除语句 /// DELETE OPENQUERY (ORASVR, 'SELECT ID,TEXT FROM t_vbtest WHERE ID = ''3'''); /// </summary> /// <param name="conn"></param> /// <param name="oraLinkName"></param> /// <param name="sql"></param> /// <returns></returns> public object UFDelete(ref ADODB.Connection conn, string oraLinkName, string tableName, string colName, string colValue) { string mSql = " DELETE FROM " + tableName + " WHERE " + colName + " = '" + colValue + "'"; object recordsAffected; conn.Execute(mSql, out recordsAffected, -1); return(recordsAffected); }
/// <summary> /// LINK 更新语句 /// UPDATE OPENQUERY (ORASVR, 'SELECT TEXT FROM t_vbtest WHERE id = 1') SET TEXT = '测试数据Update'; /// </summary> /// <param name="conn"></param> /// <param name="oraLinkName"></param> /// <param name="sql"></param> /// <returns></returns> public object LinkUpdate(ref ADODB.Connection conn, string oraLinkName, string colNames, string sql) { string mSql = " UPDATE OPENQUERY (" + oraLinkName + ", 'SELECT colNames FROM t_vbtest WHERE id = 1') SET " + sql + ";"; object recordsAffected; conn.Execute(sql, out recordsAffected, -1); return(recordsAffected); }
/// <summary> /// 删除语句 /// DELETE OPENQUERY (ORASVR, 'SELECT ID,TEXT FROM t_vbtest WHERE ID = ''3'''); /// </summary> /// <param name="conn"></param> /// <param name="oraLinkName"></param> /// <param name="sql"></param> /// <returns></returns> public object LinkDelete(ref ADODB.Connection conn, string oraLinkName, string tableName, string colName, string colValue) { string mSql = " DELETE OPENQUERY (" + oraLinkName + ", 'SELECT " + colName + " FROM " + tableName + " WHERE id = '' " + colValue + "''') ;"; object recordsAffected; conn.Execute(mSql, out recordsAffected, -1); return(recordsAffected); }
/// <summary> /// Link查询 /// select * from openquery(ORASVR,'select * from t_vbtest' /// </summary> /// <param name="sql"></param> /// <returns></returns> public object LinkSelect(ref ADODB.Connection conn, string oraLinkName, string tableName, string colNames) { string mSql = " SELECT * FROM OPENQUERY (" + oraLinkName + ",' SELECT " + colNames + " FROM " + tableName + "')"; object recordsAffected; conn.Execute(mSql, out recordsAffected, -1); return(recordsAffected); }
/// <summary> /// 插入语句 /// INSERT OPENQUERY (ORASVR, 'SELECT ID,TEXT FROM t_vbtest') VALUES ('3','测试数据Insert'); /// </summary> /// <param name="conn"></param> /// <param name="oraLinkName"></param> /// <param name="sql"></param> /// <returns></returns> public object LinkInsert(ref ADODB.Connection conn, string oraLinkName, string tableName, string colNames, string colValues) { string mSql = " INSERT OPENQUERY (" + oraLinkName + ", 'SELECT " + colNames + " FROM " + tableName + " ') VALUES( " + colValues + ") ;"; object recordsAffected; conn.Execute(mSql, out recordsAffected, -1); return(recordsAffected); }
/// <summary> /// 插入语句 /// INSERT OPENQUERY (ORASVR, 'SELECT ID,TEXT FROM t_vbtest') VALUES ('3','测试数据Insert'); /// </summary> /// <param name="conn"></param> /// <param name="oraLinkName"></param> /// <param name="sql"></param> /// <returns></returns> public object UFInsert(ref ADODB.Connection conn, string oraLinkName, string tableName, string colNames, string colValues) { string mSql = " INSERT INTO " + tableName + " ( " + colNames + " ) VALUES( " + colValues + ") "; object recordsAffected; conn.Execute(mSql, out recordsAffected, -1); return(recordsAffected); }
/// <summary> /// Link查询 /// select * from openquery(ORASVR,'select * from t_vbtest') /// </summary> /// <param name="sql"></param> /// <returns></returns> public object LinkSelect(ref ADODB.Connection conn, string oraLinkName, string sql) { string mSql = " SELECT * FROM OPENQUERY (" + oraLinkName + ",'" + sql + "')"; object recordsAffected; conn.Execute(sql, out recordsAffected, -1); return(recordsAffected); }
/// <summary> /// Link查询 /// select * from openquery(ORASVR,'select * from t_vbtest') /// </summary> /// <param name="sql"></param> /// <returns></returns> public object UFSelect(ref ADODB.Connection conn, string oraLinkName, string sql) { string mSql = sql; object recordsAffected; conn.Execute(sql, out recordsAffected, -1); return(recordsAffected); }
/// <summary> /// LINK 更新语句 /// UPDATE OPENQUERY (ORASVR, 'SELECT TEXT FROM t_vbtest WHERE id = 1') SET TEXT = '测试数据Update'; /// </summary> /// <param name="conn"></param> /// <param name="oraLinkName"></param> /// <param name="sql"></param> /// <returns></returns> public object UFUpdate(ref ADODB.Connection conn, string oraLinkName, string tableName, string colNames, string keyName, string keyValue, string setCols) { string mSql = " UPDATE " + tableName + " SET " + setCols + " WHERE " + keyName + " = '" + keyValue + "'"; object recordsAffected; conn.Execute(mSql, out recordsAffected, -1); return(recordsAffected); }
private void buildMinMax() { string db = null; string sql = null; ADODB.Recordset rs = default(ADODB.Recordset); ADODB.Connection lConn = default(ADODB.Connection); ADODB.Recordset rsData = default(ADODB.Recordset); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; modRecordSet.cnnDB.Execute("DELETE MinMaxStockItemLnk.* FROM MinMaxStockItemLnk;"); // sql = "INSERT INTO MinMaxStockItemLnk (MinMaxStockItemLnk_MinMaxID, MinMaxStockItemLnk_StockItemID, MinMaxStockItemLnk_Minimum, MinMaxStockItemLnk_Maximum, MinMaxStockItemLnk_Disabled, MinMaxStockItemLnk_Average) SELECT TOP 100 PERCENT 1, StockItem.StockItemID, 0, 0, 0, 0 FROM MinMaxStockItemLnk RIGHT OUTER JOIN StockItem ON MinMaxStockItemLnk.MinMaxStockItemLnk_StockItemID = StockItem.StockItemID Where (MinMaxStockItemLnk.MinMaxStockItemLnk_StockItemID Is Null)" modRecordSet.cnnDB.Execute("INSERT INTO MinMaxStockItemLnk ( MinMaxStockItemLnk_MinMaxID, MinMaxStockItemLnk_StockItemID, MinMaxStockItemLnk_Minimum, MinMaxStockItemLnk_Maximum, MinMaxStockItemLnk_Disabled, MinMaxStockItemLnk_Average ) SELECT 1, StockItem.StockItemID, 0, 0, 0, 0 FROM StockItem;"); // cnnDB.Execute "UPDATE MinMaxStockItemLnk SET MinMaxStockItemLnk.MinMaxStockItemLnk_Minimum = 0, MinMaxStockItemLnk.MinMaxStockItemLnk_Average = 0 WHERE (((MinMaxStockItemLnk.MinMaxStockItemLnk_MinMaxID)=1));" rs = modRecordSet.getRS(ref "SELECT DISTINCT Company.Company_MonthEndID, DayEnd.DayEnd_MonthEndID From dayEnd, Company WHERE (((DayEnd.DayEndID)>=" + My.MyProject.Forms.frmOrderWizardFilter.gDayEndStart + " And (DayEnd.DayEndID)<=" + My.MyProject.Forms.frmOrderWizardFilter.gDayEndEnd + "));"); while (!(rs.EOF)) { if (rs.Fields("Company_MonthEndID").Value == rs.Fields("DayEnd_MonthEndID").Value) { db = "pricing.mdb"; } else { db = "month" + rs.Fields("DayEnd_MonthEndID").Value + ".mdb"; } lConn = modRecordSet.openConnectionInstance(ref Convert.ToString(db)); if (lConn == null) { } else { lConn.Execute("UPDATE DayEndStockItemLnk INNER JOIN MinMaxStockItemLnk ON DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = MinMaxStockItemLnk.MinMaxStockItemLnk_StockItemID SET MinMaxStockItemLnk.MinMaxStockItemLnk_Average = [MinMaxStockItemLnk_Average]+[DayEndStockItemLnk_QuantitySales] WHERE (((DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)>=" + My.MyProject.Forms.frmOrderWizardFilter.gDayEndStart + " And (DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<=" + My.MyProject.Forms.frmOrderWizardFilter.gDayEndEnd + "));"); lConn.Execute("UPDATE StockBreakTransaction INNER JOIN MinMaxStockItemLnk ON StockBreakTransaction.StockBreakTransaction_ParentID = MinMaxStockItemLnk.MinMaxStockItemLnk_StockItemID SET MinMaxStockItemLnk.MinMaxStockItemLnk_Average = [MinMaxStockItemLnk_Average]+[StockBreakTransaction_MoveQuantity] WHERE (((StockBreakTransaction.StockBreakTransaction_DayEndID)>=" + My.MyProject.Forms.frmOrderWizardFilter.gDayEndStart + " And (StockBreakTransaction.StockBreakTransaction_DayEndID)<=" + My.MyProject.Forms.frmOrderWizardFilter.gDayEndEnd + "));"); lConn.Execute("UPDATE StockBreakTransaction INNER JOIN MinMaxStockItemLnk ON StockBreakTransaction.StockBreakTransaction_ChildID = MinMaxStockItemLnk.MinMaxStockItemLnk_StockItemID SET MinMaxStockItemLnk.MinMaxStockItemLnk_Average = [MinMaxStockItemLnk_Average]-([StockBreakTransaction_MoveQuantity]*[StockBreakTransaction_Quantity]) WHERE (((StockBreakTransaction.StockBreakTransaction_DayEndID)>=" + My.MyProject.Forms.frmOrderWizardFilter.gDayEndStart + " And (StockBreakTransaction.StockBreakTransaction_DayEndID)<=" + My.MyProject.Forms.frmOrderWizardFilter.gDayEndEnd + "));"); } rs.moveNext(); } modRecordSet.cnnDB.Execute("UPDATE MinMaxStockItemLnk SET MinMaxStockItemLnk.MinMaxStockItemLnk_Average = [MinMaxStockItemLnk_Average]/" + Convert.ToDouble(My.MyProject.Forms.frmOrderWizardFilter.gDayEndEnd) - My.MyProject.Forms.frmOrderWizardFilter.gDayEndStart + 1 + ";"); modRecordSet.cnnDB.Execute("UPDATE MinMaxStockItemLnk SET MinMaxStockItemLnk.MinMaxStockItemLnk_Minimum = [MinMaxStockItemLnk_Average]*" + My.MyProject.Forms.frmOrderWizardFilter.txtDays.Text + ";"); modRecordSet.cnnDB.Execute("UPDATE MinMaxStockItemLnk INNER JOIN MinMax ON MinMaxStockItemLnk.MinMaxStockItemLnk_MinMaxID = MinMax.MinMaxID SET MinMaxStockItemLnk.MinMaxStockItemLnk_Minimum = [MinMax]![MinMax_Minimum] WHERE (((MinMaxStockItemLnk.MinMaxStockItemLnk_Minimum)<[MinMax]![MinMax_Minimum]));"); modRecordSet.cnnDB.Execute("DELETE StockItem.StockItem_Disabled, MinMaxStockItemLnk.* FROM MinMaxStockItemLnk INNER JOIN StockItem ON MinMaxStockItemLnk.MinMaxStockItemLnk_StockItemID = StockItem.StockItemID WHERE (((StockItem.StockItem_Disabled)<>0));"); modRecordSet.cnnDB.Execute("DELETE StockItem.StockItem_Disabled, MinMaxStockItemLnk.* FROM MinMaxStockItemLnk INNER JOIN StockItem ON MinMaxStockItemLnk.MinMaxStockItemLnk_StockItemID = StockItem.StockItemID WHERE (((StockItem.StockItem_Discontinued)<>0));"); modRecordSet.cnnDB.Execute("UPDATE MinMaxStockItemLnk INNER JOIN StockItem ON MinMaxStockItemLnk.MinMaxStockItemLnk_StockItemID = StockItem.StockItemID SET MinMaxStockItemLnk.MinMaxStockItemLnk_Disabled = 1 WHERE (((StockItem.StockItem_OrderDynamic)<>0));"); modRecordSet.cnnDB.Execute("UPDATE MinMaxStockItemLnk INNER JOIN StockItem ON MinMaxStockItemLnk.MinMaxStockItemLnk_StockItemID = StockItem.StockItemID SET MinMaxStockItemLnk.MinMaxStockItemLnk_Minimum = [StockItem]![StockItem_MinimumStock] WHERE (((MinMaxStockItemLnk.MinMaxStockItemLnk_Disabled)<>0));"); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; }
private void ExecuteSQL(string sql) { object objOut; //don't execute any sql after a cancel is issued, unless it is a drop schema if ((bwkr.CancellationPending == false) || (bwkr.CancellationPending == true && sql.IndexOf("DROP SCHEMA") > -1)) { conMySQL.Execute(sql, out objOut, 0); // MMM } }
//-------------------------------------------------------------------------------------------- /// <summary> /// This method simplifies some of the redundant tasks involved with quering the database. /// </summary> /// <param name="sqlquery">This is the sql statement that will be queried against the database.</param> /// <returns>A recordset encapsulating the results of the query is returned.</returns> private ADODB.Recordset Query(string sqlquery) { try { System.Diagnostics.Debug.WriteLine(sqlquery, "SQL Query"); object ob; return(db.Execute(sqlquery, out ob, -1)); } catch { // returns a new emtpy recordset class, returning null value is not recommened as it would require much more // handling code return(new ADODB.RecordsetClass()); } }
public static bool RunSqlAdo(ADODB.Connection openConn, string queryString) { object ret; bool success = false; try { try { openConn.Execute(queryString, out ret, 0); } catch (Exception ex) { throw new Exception("Error in executing ado command. Command string: " + queryString + ". Exception message: " + ex.Message, ex); } success = true; } catch (Exception ex) { LogFiles logFiles = new LogFiles(); logFiles.ErrorLog(ex); } return(success); }
public void Insert(string query) { try { System.Threading.Monitor.Enter(lockObj); object ret; conn.Open(connectionString, "", "", -1); conn.Execute(query, out ret, 0); } catch (Exception ex) { string error = "Ошибка при инсерте в БД"; Logger.Write($"\n{error}:\n{new string('-', 10)}\n{ex.Message}\n{new string('-', 10)}"); } finally { conn.Close(); System.Threading.Monitor.Exit(lockObj); } }
//ADODB.Connection conn; public void SimulatePluginBusiness(ref ADODB.Connection conn) { try { string sql; sql = "DELETE FROM AA_Enum_UAPEventTest WHERE cProjectNO = 'CSharpSync'; \r\n" + "INSERT INTO AA_Enum_UAPEventTest(\r\n" + " EnumType, EnumCode, LocaleId, EnumName, EnumIndex, cProjectNO, cTimeStamp)\r\n" + "SELECT TOP 5 \r\n" + " NEWID() AS EnumType, EnumCode, LocaleId, EnumName, EnumIndex, 'CSharpSync' AS cProjectNO, GETDATE() AS cTimeStamp\r\n" + "FROM AA_Enum "; object recordsAffected; conn.Execute(sql, out recordsAffected, -1); } catch (Exception ex) { //Output.mWriteLog(ex.Message); throw ex; } }
public static bool ExecSql(string sql) { try { ADODB.Connection Conn = GeneralCommon.M_CN1; //Db Connection Check if (Conn.State == 0) { if (GeneralCommon.GF_DbConnect() == false) { return(false); } } object null_object = null; Conn.Execute(sql, out null_object, -1); return(true); } catch (Exception ex) { GeneralCommon.Gp_MsgBoxDisplay((string)("ExecSql Error : " + ex.Message), "", ""); return(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; }
public static void WriteDB(List <xl2cad_cad.midTable> tables) { string filePath = @"D:\xl2cad.mdb"; string mdbCommand = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5"; ADODB.Connection cn = null; ADOX.CatalogClass cat = null; try { //创建数据库,有就删除重建,没有则新建 cat = new CatalogClass(); if (File.Exists(filePath)) { File.Delete(filePath); } cat.Create(mdbCommand); //连接数据库 cn = new ADODB.Connection(); cat = null; cat = new CatalogClass(); //创建数据表 foreach (xl2cad_cad.midTable midtable in tables) { cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1); cat.ActiveConnection = cn; ADOX.TableClass table = new TableClass(); string tablename = midtable.Name; table.ParentCatalog = cat; table.Name = tablename; cat.Tables.Append(table); //将数据写入数据表 //Excel.WorkSheet.Range导出的object[obj1,obj2]中,obj1为行,obj2为列 //因此先按obj2的个数建立字段,在按obj1的个数一行行填入数据 object[,] datas = midtable.data; int rowCount = 0; int columnCount = 0; rowCount = datas.GetUpperBound(0); //第一维obj1的最大值,行数 columnCount = datas.GetUpperBound(1); //第二维obj2的最大值,列数 //建立字段 for (int i = 0; i <= columnCount; i++) { ADOX.ColumnClass col = null; col = new ADOX.ColumnClass(); col.ParentCatalog = cat; col.Properties["Jet OLEDB:Allow Zero Length"].Value = true; col.Name = "Value" + i; table.Columns.Append(col, ADOX.DataTypeEnum.adVarChar, 25); } //按行填入数据 object ra = null; ADODB.Recordset rs = new ADODB.Recordset(); for (int i = 0; i <= rowCount; i++) { //构造按行写入的sql语句 string sql1 = String.Format("INSERT INTO {0} (", tablename); string sql2 = String.Format(") VALUES ("); string strValue = null; for (int j = 0; j <= columnCount; j++) { if (datas[i, j] == null) { strValue = datas[i, j] as string; } else { strValue = datas[i, j].ToString(); } sql1 += String.Format("Value{0},", j); sql2 += String.Format("'{0}',", strValue); } string sql = sql1 + sql2 + ")"; //将 ,) 替换为 ) sql = sql.Replace(",)", ")"); rs = cn.Execute(sql, out ra, -1); } } } catch (System.Exception ex) { MessageBox.Show(ex.ToString()); } finally { cn.Close(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cn); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat); System.GC.Collect(); } }
/// <summary> /// Removes all entries from given table /// </summary> /// <param name="table">Table name</param> /// <returns>True if success</returns> public bool DeleteAllData(string table) { string strSQL = string.Format(DELETE_ALL, table); ADODB.Connection con = new ADODB.Connection(); object obj = new object(); try { con.Open(GenerateConnectionString(), "", "", 0); } catch (Exception) { //TODO: Error logging return false; } try { con.Execute(strSQL, out obj, 0); } catch (Exception) { //TODO: Error logging con.Close(); return false; } con.Close(); return true; }
/// <summary> /// Saves TAGs list into database /// </summary> /// <param name="tagsArray">List of TAGs</param> /// <returns>True if success</returns> public bool SaveTags(Tag[] tagsArray) { string strSQL; ADODB.Connection con = new ADODB.Connection(); object obj = new object(); try { con.Open(GenerateConnectionString(), "", "", 0); } catch (Exception) { //TODO: Error logging return false; } for (int i = 0; i < tagsArray.Length; i++) { strSQL = string.Format(NEW_TAG, tagsArray[i].Id, tagsArray[i].Name); try { con.Execute(strSQL, out obj, 0); } catch (Exception) { //TODO: Error logging con.Close(); return false; } } con.Close(); return true; }
/// <summary> /// Saves relations between TAGs and XML rows /// </summary> /// <param name="relationsArray">List with TAG IDs and XML FIELD IDs</param> /// <returns>True if success</returns> public bool SaveRelations(XmlTagRelation[] relationsArray) { string strSQL; ADODB.Connection con = new ADODB.Connection(); object obj = new object(); try { con.Open(GenerateConnectionString(), "", "", 0); } catch (Exception) { //TODO: Error logging return false; } for (int i = 0; i < relationsArray.Length; i++) { strSQL = string.Format(NEW_XML_TAG_RELATION, relationsArray[i].TagID, relationsArray[i].XmlFileID); try { con.Execute(strSQL, out obj, 0); } catch (Exception) { //TODO: Error logging con.Close(); return false; } } con.Close(); return true; }
/// <summary> /// Saves information about XML Files into database /// </summary> /// <param name="filesArray">List with informations about XML Files</param> /// <returns>True if success</returns> public bool SaveFiles(XmlFile[] filesArray) { string strSQL; ADODB.Connection con = new ADODB.Connection(); object obj = new object(); try { con.Open(GenerateConnectionString(), "", "", 0); } catch (Exception) { return false; } for (int i = 0; i < filesArray.Length; i++) { strSQL = string.Format(NEW_XML_FILE, filesArray[i].Id, filesArray[i].Name, filesArray[i].Content, filesArray[i].TimeStamp); try { con.Execute(strSQL, out obj, 0); } catch (Exception) { //TODO: Error logging con.Close(); return false; } } con.Close(); return true; }
private void btnsave_Click(object sender, EventArgs e) { ADODB.Recordset tmp = new ADODB.Recordset(); try { if (ADOconn.State == 0) { ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0); } ADODB.Recordset rec = new ADODB.Recordset(); Conn.Close(); // Conn.Open(); bool isempty; isempty = false; if (isedit) { if (txtpriv.Text.Substring(1, 1) == "0") { MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges "); return; } } else { if (txtpriv.Text.Substring(0, 1) == "0") { MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges "); return; } } if (isempty) { MessageBox.Show("Entry Not Completed, Please fill all Yellow Marked fileds!!", "Invalid Entry"); return; } try { // ADOconn.BeginTrans(); //if (cmbmonth.SelectedIndex<0) //{ // MessageBox.Show("Invalid Salary Month, Please Select a Valid Month", "Invalid Entry"); // return; //} ADOconn.BeginTrans(); int i = 0; string entry_no = dtentry.Value.Date.ToString("yyyyMMdd"); if (isedit) { sql = "delete from reconcil_det where Reconcil_No = '" + entry_no + "'"; object a; ADOconn.Execute(sql, out a); } sql = "select * from reconcil_det where Reconcil_No = '" + entry_no + "'"; rec = new ADODB.Recordset(); rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1); if (rec.RecordCount == 0) { // rec.AddNew(); } else { MessageBox.Show("Entry Already Exist , Cannot enter as a New entry", "Invalid Entry"); return; } for (i = 0; i < dgv1.Rows.Count; i++) { if (dgv1[0, i].Value != null) { rec.AddNew(); if (dgv1[3, i].Value == null) { dgv1[3, i].Value = 0; } if (dgv1[4, i].Value == null) { dgv1[4, i].Value = 0; } double amt = Convert.ToDouble(dgv1[3, i].Value) + Convert.ToDouble(dgv1[4, i].Value); rec.Fields["Reconcil_No"].Value = entry_no; rec.Fields["Reconcil_Date"].Value = dtentry.Value.Date; rec.Fields["Reconcil_user"].Value = Gvar.username; rec.Fields["Amount"].Value = Math.Abs(amt); rec.Fields["Bank_No"].Value = cmbbank.SelectedValue; rec.Fields["DR_CR"].Value = dgv1[2, i].Value; rec.Fields["plus_minus"].Value = ""; rec.Fields["pay_date"].Value = dgv1[0, i].Value; rec.Fields["SNO"].Value = i + 1; rec.Fields["trn_type"].Value = "A"; rec.Fields["NARRATION"].Value = dgv1[5, i].Value; rec.Update(); } } for (i = 0; i < dgvbank.Rows.Count; i++) { if (dgvbank[2, i].Value != null) { rec.AddNew(); if (dgvbank[0, i].Value == null) { dgvbank[0, i].Value = ""; } if (dgvbank[1, i].Value == null) { dgvbank[1, i].Value = 0; } double amt = Convert.ToDouble(dgvbank[2, i].Value); rec.Fields["Reconcil_No"].Value = entry_no; rec.Fields["Reconcil_Date"].Value = dtentry.Value.Date; rec.Fields["Reconcil_user"].Value = Gvar.username; rec.Fields["Amount"].Value = Math.Abs(amt); rec.Fields["Bank_No"].Value = cmbbank.SelectedValue; if (dgvbank[1, i].Value == "+") { rec.Fields["DR_CR"].Value = "D"; } else { rec.Fields["DR_CR"].Value = "C"; } rec.Fields["plus_minus"].Value = dgvbank[1, i].Value; rec.Fields["pay_date"].Value = ""; rec.Fields["SNO"].Value = i + 1; rec.Fields["trn_type"].Value = "B"; rec.Fields["NARRATION"].Value = dgvbank[0, i].Value; rec.Update(); } } for (i = 0; i < dgvbook.Rows.Count; i++) { if (dgvbook[2, i].Value != null) { rec.AddNew(); if (dgvbook[0, i].Value == null) { dgvbook[0, i].Value = ""; } if (dgvbook[1, i].Value == null) { dgvbook[1, i].Value = 0; } double amt = Convert.ToDouble(dgvbook[2, i].Value); rec.Fields["Reconcil_No"].Value = entry_no; rec.Fields["Reconcil_Date"].Value = dtentry.Value.Date; rec.Fields["Reconcil_user"].Value = Gvar.username; rec.Fields["Amount"].Value = Math.Abs(amt); rec.Fields["Bank_No"].Value = cmbbank.SelectedValue; if (dgvbook[1, i].Value == "+") { rec.Fields["DR_CR"].Value = "D"; } else { rec.Fields["DR_CR"].Value = "C"; } rec.Fields["plus_minus"].Value = dgvbook[1, i].Value; rec.Fields["pay_date"].Value = ""; rec.Fields["SNO"].Value = i + 1; rec.Fields["trn_type"].Value = "C"; rec.Fields["NARRATION"].Value = dgvbook[0, i].Value; rec.Update(); } } ADOconn.CommitTrans(); isedit = true; MessageBox.Show("Successfully Saved"); } catch (Exception ex) { ADOconn.RollbackTrans(); MessageBox.Show(ex.Message); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void cmdTransfer_Click(System.Object eventSender, System.EventArgs eventArgs) { int grvNo = 0; string sql = null; ADODB.Recordset rsID = default(ADODB.Recordset); ADODB.Recordset rs = default(ADODB.Recordset); short x = 0; ADODB.Connection cn = default(ADODB.Connection); ADODB.Recordset rsChk = default(ADODB.Recordset); string errPosition = null; // ERROR: Not supported in C#: OnErrorStatement errPosition = "Start"; if (lvStockT.Items.Count > 0) { errPosition = "1"; sql = "INSERT INTO PurchaseOrder ( PurchaseOrder_SupplierID, PurchaseOrder_DayEndID, PurchaseOrder_PersonID, PurchaseOrder_DateCreated, PurchaseOrder_PurchaseOrderStatusID, PurchaseOrder_Reference, PurchaseOrder_AttentionLine ) "; sql = sql + "SELECT 2, Company.Company_DayEndID, " + modRecordSet.gPersonID + ", Now(), 1, '" + Strings.Format(DateAndTime.Now, "yyyy mmm dd") + " (Blind)', '' FROM Company;"; modRecordSet.cnnDB.Execute(sql, , ADODB.ExecuteOptionEnum.adExecuteNoRecords); errPosition = "2"; rsID = modRecordSet.getRS(ref "SELECT Max(PurchaseOrder.PurchaseOrderID) AS id FROM PurchaseOrder;"); grvNo = "Trsnfr#-" + rsID.Fields("id").Value; sql = "INSERT INTO GRV ( GRV_PurchaseOrderID, GRV_DayEndID, GRV_GRVStatusID, GRV_Date, GRV_InvoiceDate, GRV_InvoiceNumber, GRV_InvoiceInclusive, GRV_InvoiceVat, GRV_InvoiceDiscount, GRV_Ullage, GRV_SundriesPlus, GRV_SundriesMinus, GRV_Terms ) SELECT " + rsID.Fields("id").Value + ", Company.Company_DayEndID, 1 AS status, Now(), Now(), 'Trsnfr#-" + rsID.Fields("id").Value + "', 0, 0, 0, 0, 0, 0, 1 FROM Company;"; modRecordSet.cnnDB.Execute(sql, , ADODB.ExecuteOptionEnum.adExecuteNoRecords); if (rsID.State) { rsID.Close(); } rsID = modRecordSet.getRS(ref "SELECT Max(GRV.GRVID) AS id FROM GRV;"); x = 1; rs = modRecordSet.getRS(ref "SELECT StockItem.StockItem_Name, StockTransferGRV.* FROM StockTransferGRV INNER JOIN StockItem ON StockTransferGRV.StockTransfer_StockItemID = StockItem.StockItemID ORDER BY StockItem.StockItem_Name, StockTransferGRV.StockTransfer_Quantity;"); while (!(rs.EOF)) { errPosition = "3"; rsChk = modRecordSet.getRS(ref "SELECT StockItem.StockItem_Name, CatalogueChannelLnk.CatalogueChannelLnk_Quantity, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (StockItem.StockItem_Quantity = Catalogue.Catalogue_Quantity) AND (Catalogue.Catalogue_StockItemID = StockItem.StockItemID)) INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" + rs.Fields("StockTransfer_StockItemID").Value + ") AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));"); if (rsChk.RecordCount) { errPosition = "4"; //changed again 04-oct- sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " & x & ", " & rsID("id") & " AS grvid, StockItem.StockItemID, 1 AS return, StockItem.StockItem_Name, '' AS code, StockItem.StockItem_Quantity, 1, " & rs("StockTransfer_Quantity") & " AS quantity, StockItem.StockItem_ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (StockItem.StockItem_Quantity = Catalogue.Catalogue_Quantity) AND (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))" sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " + x + ", " + rsID.Fields("id").Value + " AS grvid, StockItem.StockItemID, 1 AS return, StockItem.StockItem_Name, '' AS code, 1, 1, " + rs.Fields("StockTransfer_Quantity").Value + " AS quantity, StockItem.StockItem_ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (StockItem.StockItem_Quantity = Catalogue.Catalogue_Quantity) AND (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))"; sql = sql + " INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" + rs.Fields("StockTransfer_StockItemID").Value + ") AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));"; Debug.Print(sql); modRecordSet.cnnDB.Execute(sql); //update selling price sql = "UPDATE GRVItem INNER JOIN PriceChannelLnk ON PriceChannelLnk.PriceChannelLnk_StockItemID = GRVItem.GRVItem_StockItemID SET GRVItem.GRVItem_Price = PriceChannelLnk.PriceChannelLnk_Price WHERE ((PriceChannelLnk.PriceChannelLnk_Quantity=1) AND (PriceChannelLnk.PriceChannelLnk_ChannelID=1) AND (GRVItem.GRVItem_GRVID)=" + rsID.Fields("id").Value + " AND GRVItem.GRVItem_Line =" + x + ");"; modRecordSet.cnnDB.Execute(sql); } else { errPosition = "5"; if (rs.Fields("StockTransfer_Pack").Value == 1) { //changed Packsize to SuppQty as Markus said it should always xfer singles(11-may-11 naresh) sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " + x + ", " + rsID.Fields("id").Value + " AS grvid, StockItem.StockItemID, 1 AS return, StockItem.StockItem_Name, '' AS code, 1, 1, " + rs.Fields("StockTransfer_Quantity").Value + " AS quantity, StockItem.StockItem_ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))"; sql = sql + " INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" + rs.Fields("StockTransfer_StockItemID").Value + ") AND ((Catalogue.Catalogue_Quantity)=1) AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));"; } else { //changed Packsize to SuppQty as Markus said it should always xfer singles(11-may-11 naresh) sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " + x + ", " + rsID.Fields("id").Value + " AS grvid, StockItem.StockItemID, 1 AS return, StockItem.StockItem_Name, '' AS code, 1, 1, " + rs.Fields("StockTransfer_Quantity").Value + " AS quantity, StockItem.StockItem_ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))"; sql = sql + " INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" + rs.Fields("StockTransfer_StockItemID").Value + ") AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));"; } Debug.Print(sql); modRecordSet.cnnDB.Execute(sql); //update selling price sql = "UPDATE GRVItem INNER JOIN PriceChannelLnk ON PriceChannelLnk.PriceChannelLnk_StockItemID = GRVItem.GRVItem_StockItemID SET GRVItem.GRVItem_Price = PriceChannelLnk.PriceChannelLnk_Price WHERE ((PriceChannelLnk.PriceChannelLnk_Quantity=1) AND (PriceChannelLnk.PriceChannelLnk_ChannelID=1) AND (GRVItem.GRVItem_GRVID)=" + rsID.Fields("id").Value + " AND GRVItem.GRVItem_Line =" + x + ");"; modRecordSet.cnnDB.Execute(sql); } x = x + 1; rs.moveNext(); } if (string.IsNullOrEmpty(loadDBStr)) { } else { cn = modRecordSet.openSComp(ref loadDBStr); errPosition = "6"; if (cn == null) { } else { errPosition = "7"; sql = "INSERT INTO PurchaseOrder ( PurchaseOrder_SupplierID, PurchaseOrder_DayEndID, PurchaseOrder_PersonID, PurchaseOrder_DateCreated, PurchaseOrder_PurchaseOrderStatusID, PurchaseOrder_Reference, PurchaseOrder_AttentionLine ) "; sql = sql + "SELECT 2, Company.Company_DayEndID, " + modRecordSet.gPersonID + ", Now(), 1, '" + Strings.Format(DateAndTime.Now, "yyyy mmm dd") + " (Blind)', '' FROM Company;"; cn.Execute(sql, , ADODB.ExecuteOptionEnum.adExecuteNoRecords); errPosition = "8"; rsID = modRecordSet.getRSwaitron(ref "SELECT Max(PurchaseOrder.PurchaseOrderID) AS id FROM PurchaseOrder;", ref cn); sql = "INSERT INTO GRV ( GRV_PurchaseOrderID, GRV_DayEndID, GRV_GRVStatusID, GRV_Date, GRV_InvoiceDate, GRV_InvoiceNumber, GRV_InvoiceInclusive, GRV_InvoiceVat, GRV_InvoiceDiscount, GRV_Ullage, GRV_SundriesPlus, GRV_SundriesMinus, GRV_Terms ) SELECT " + rsID.Fields("id").Value + ", Company.Company_DayEndID, 1 AS status, Now(), Now(), 'Trsnfr#-" + rsID.Fields("id").Value + "', 0, 0, 0, 0, 0, 0, 1 FROM Company;"; cn.Execute(sql, , ADODB.ExecuteOptionEnum.adExecuteNoRecords); if (rsID.State) { rsID.Close(); } rsID = modRecordSet.getRSwaitron(ref "SELECT Max(GRV.GRVID) AS id FROM GRV;", ref cn); x = 1; rs = modRecordSet.getRS(ref "SELECT StockItem.StockItem_Name, StockTransferGRV.* FROM StockTransferGRV INNER JOIN StockItem ON StockTransferGRV.StockTransfer_StockItemID = StockItem.StockItemID ORDER BY StockItem.StockItem_Name, StockTransferGRV.StockTransfer_Quantity;"); while (!(rs.EOF)) { errPosition = "9"; //Set rsChk = getRS("SELECT StockItem.StockItem_Name, CatalogueChannelLnk.CatalogueChannelLnk_Quantity, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (StockItem.StockItem_Quantity = Catalogue.Catalogue_Quantity) AND (Catalogue.Catalogue_StockItemID = StockItem.StockItemID)) INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" & rs("StockTransfer_StockItemID") & ") AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));") rsChk = modRecordSet.getRSwaitron(ref "SELECT StockItem.StockItem_Name, CatalogueChannelLnk.CatalogueChannelLnk_Quantity, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (StockItem.StockItem_Quantity = Catalogue.Catalogue_Quantity) AND (Catalogue.Catalogue_StockItemID = StockItem.StockItemID)) INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" + rs.Fields("StockTransfer_StockItemID").Value + ") AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));", ref cn); if (rsChk.RecordCount) { errPosition = "10"; //list cost will also change 04-oct sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " & x & ", " & rsID("id") & " AS grvid, StockItem.StockItemID, 0 AS return, StockItem.StockItem_Name, '' AS code, 1, 1, " & rs("StockTransfer_Quantity") & " AS quantity, StockItem.StockItem_ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (StockItem.StockItem_Quantity = Catalogue.Catalogue_Quantity) AND (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))" sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " + x + ", " + rsID.Fields("id").Value + " AS grvid, StockItem.StockItemID, 0 AS return, StockItem.StockItem_Name, '' AS code, 1, 1, " + rs.Fields("StockTransfer_Quantity").Value + " AS quantity, " + rs.Fields("StockTransfer_Price").Value + " AS ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (StockItem.StockItem_Quantity = Catalogue.Catalogue_Quantity) AND (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))"; sql = sql + " INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" + rs.Fields("StockTransfer_StockItemID").Value + ") AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));"; cn.Execute(sql); //update selling price sql = "UPDATE GRVItem INNER JOIN PriceChannelLnk ON PriceChannelLnk.PriceChannelLnk_StockItemID = GRVItem.GRVItem_StockItemID SET GRVItem.GRVItem_Price = PriceChannelLnk.PriceChannelLnk_Price WHERE ((PriceChannelLnk.PriceChannelLnk_Quantity=1) AND (PriceChannelLnk.PriceChannelLnk_ChannelID=1) AND (GRVItem.GRVItem_GRVID)=" + rsID.Fields("id").Value + " AND GRVItem.GRVItem_Line =" + x + ");"; cn.Execute(sql); } else { errPosition = "11"; if (rs.Fields("StockTransfer_Pack").Value == 1) { //changed Packsize to SuppQty as Markus said it should always xfer singles(11-may-11 naresh) //changed again 04-oct- sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " & x & ", " & rsID("id") & " AS grvid, StockItem.StockItemID, 0 AS return, StockItem.StockItem_Name, '' AS code, StockItem.StockItem_Quantity, 1, " & rs("StockTransfer_Quantity") & " AS quantity, StockItem.StockItem_ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))" sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " + x + ", " + rsID.Fields("id").Value + " AS grvid, StockItem.StockItemID, 0 AS return, StockItem.StockItem_Name, '' AS code, 1, 1, " + rs.Fields("StockTransfer_Quantity").Value + " AS quantity, " + rs.Fields("StockTransfer_Price").Value + " AS ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))"; sql = sql + " INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" + rs.Fields("StockTransfer_StockItemID").Value + ") AND ((Catalogue.Catalogue_Quantity)=1) AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));"; } else { //changed Packsize to SuppQty as Markus said it should always xfer singles(11-may-11 naresh) //changed again 04-oct- sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " & x & ", " & rsID("id") & " AS grvid, StockItem.StockItemID, 0 AS return, StockItem.StockItem_Name, '' AS code, StockItem.StockItem_Quantity, 1, " & rs("StockTransfer_Quantity") & " AS quantity, StockItem.StockItem_ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))" sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " + x + ", " + rsID.Fields("id").Value + " AS grvid, StockItem.StockItemID, 0 AS return, StockItem.StockItem_Name, '' AS code, 1, 1, " + rs.Fields("StockTransfer_Quantity").Value + " AS quantity, " + rs.Fields("StockTransfer_Price").Value + " AS ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))"; sql = sql + " INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" + rs.Fields("StockTransfer_StockItemID").Value + ") AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));"; } Debug.Print(sql); cn.Execute(sql); //update selling price sql = "UPDATE GRVItem INNER JOIN PriceChannelLnk ON PriceChannelLnk.PriceChannelLnk_StockItemID = GRVItem.GRVItem_StockItemID SET GRVItem.GRVItem_Price = PriceChannelLnk.PriceChannelLnk_Price WHERE ((PriceChannelLnk.PriceChannelLnk_Quantity=1) AND (PriceChannelLnk.PriceChannelLnk_ChannelID=1) AND (GRVItem.GRVItem_GRVID)=" + rsID.Fields("id").Value + " AND GRVItem.GRVItem_Line =" + x + ");"; cn.Execute(sql); //sql = "INSERT INTO GRVItem ( GRVItem_Line, GRVItem_GRVID, GRVItem_StockItemID, GRVItem_Return, GRVItem_Name, GRVItem_Code, GRVItem_PackSize, GRVItem_QuantityOrder, GRVItem_Quantity, GRVItem_ContentCost, GRVItem_DiscountAmount, GRVItem_WarehouseQuantity, GRVItem_ActualCost, GRVItem_Date, GRVItem_VatRate, GRVItem_Price ) SELECT " & x & ", " & rsID("id") & " AS grvid, StockItem.StockItemID, 0 AS return, StockItem.StockItem_Name, '' AS code, 1, 1, " & rs("StockTransfer_Quantity") & " AS quantity, StockItem.StockItem_ListCost, 0 AS discount, 0 AS whQuantity, 0 AS actualCost, Now() AS [date], 0 AS vatRate, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM (Catalogue INNER JOIN StockItem ON (Catalogue.Catalogue_StockItemID = StockItem.StockItemID))" //sql = sql & " INNER JOIN CatalogueChannelLnk ON (Catalogue.Catalogue_StockItemID = CatalogueChannelLnk.CatalogueChannelLnk_StockItemID) AND (Catalogue.Catalogue_Quantity = CatalogueChannelLnk.CatalogueChannelLnk_Quantity) WHERE (((StockItem.StockItemID)=" & rs("StockTransfer_StockItemID") & ") AND ((CatalogueChannelLnk.CatalogueChannelLnk_ChannelID)=1));" //cn.Execute sql } Debug.Print(sql); x = x + 1; rs.moveNext(); } } } Interaction.MsgBox("GRV on both Locations has been created with InvoiceNumber " + grvNo + ". Please process ASAP.", MsgBoxStyle.Information + MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton2, "Completed"); this.Close(); } else { } return; ErrTransfer: Interaction.MsgBox("Error at position no. " + errPosition + " Err Number " + Err().Number + " " + Err().Description); // ERROR: Not supported in C#: ResumeStatement }
private void doMode(ref short mode) { string strFldName = null; string st1 = null; // ERROR: Not supported in C#: OnErrorStatement int gParameters = 0; bool bHOAutoUpload = false; ADODB.Recordset rsBit = default(ADODB.Recordset); ADODB.Recordset rs = default(ADODB.Recordset); gMode = mode; int x = 0; int gMonth = 0; int dayStart = 0; int dayEnd = 0; string sql = null; string errDesc = null; const short gParPastelReport = 128; //Pastel CSV's const short gCopySalesToHQ = 1024; //Sales to HQ const short gZeroStock_DayEnd = 4096; 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; errDesc = "Starting Point"; ADODB.Connection cn = new ADODB.Connection(); bool bDCChk = false; ADODB.Recordset rsDCChk = default(ADODB.Recordset); ADODB.Recordset rsRep = default(ADODB.Recordset); ADODB.Recordset rsHO = default(ADODB.Recordset); Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); Scripting.TextStream lTextstream = default(Scripting.TextStream); string lString = null; //Dim Report As New cryOpenTable CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); Report.Load("cryOpenTable"); switch (gMode) { case mdPOS: errDesc = "mdPOS Point"; //Check for open table... cn = modRecordSet.openConnectionInstance(ref "waitron.mdb"); if (cn == null) { Interaction.MsgBox("The Day End cannot be successfully executed as the Waitron Database is unable to connect to the 4POS BackOffice server." + Constants.vbCrLf + "Please ensure that the database is at correct location." + Constants.vbCrLf + Constants.vbCrLf + "If this error persists please contact the '4POS' representative.", MsgBoxStyle.Critical, "Server Off-line"); return; } //Clear WaitronTable cn.Execute("DELETE * FROM WaitronTable;"); //Dry Cleaning Customer Check bDCChk = false; rsDCChk = modRecordSet.getRS(ref "SELECT * FROM Company;"); if (rsDCChk.RecordCount) { if (rsDCChk.Fields.Count >= 60) { if (rsDCChk.Fields("Company_DCCustomer").Value) { bDCChk = true; } } } //Dry Cleaning Customer Check rs = modRecordSet.getRSwaitron(ref "SELECT * FROM OpenTable", ref cn); if (rs.RecordCount > 0 & bDCChk == false) { st1 = "You cannot do Day End now, because there are still some Open Table" + Constants.vbCrLf + Constants.vbCrLf; // on this machine st1 = st1 + "Make sure all Waitron's had cashout to all their table(s)" + Constants.vbCrLf + Constants.vbCrLf; st1 = st1 + "Click OK to see table(s) list"; Interaction.MsgBox(st1, MsgBoxStyle.Exclamation, "Day End Warning"); //Dim sql As String System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; rsRep = modRecordSet.getRS(ref "SELECT * FROM Company"); //Report.txtCompanyName.SetText rsRep("Company_Name") rsRep.Close(); sql = "SELECT OpenTable.OpenTable_TableID, OpenTable.OpenTable_WaitronID, OpenTable.OpenTable_Text, OpenTable.OpenTable_Date, OpenTable.OpenTable_GuestCount, TableTranactionItem.TableTranactionItem_lineNo, TableTranactionItem.TableTranactionItem_name, TableTranactionItem.TableTranactionItem_quantity, TableTranactionItem.TableTranactionItem_price, Person.Person_FirstName, Person.Person_LastName "; sql = sql + "FROM (OpenTable OpenTable INNER JOIN Person Person ON OpenTable.OpenTable_WaitronID = Person.PersonID) INNER JOIN TableTranactionItem TableTranactionItem ON OpenTable.OpenTable_TableID = TableTranactionItem.TableTranactionItem_TableID ORDER BY OpenTable.OpenTable_TableID ASC;"; rsRep = modRecordSet.getRSwaitron(ref sql, ref cn); //If rs.BOF Or rs.EOF Then // ReportNone.Load("cryNoRecords.rpt") // ReportNone.txtCompanyName.SetText Report.txtCompanyName.Text // ReportNone.txtTitle.SetText Report.txtTitle.Text // frmReportShow.caption = ReportNone.txtTitle.Text // frmReportShow.CRViewer1.ReportSource = ReportNone // Set frmReportShow.mReport = ReportNone: frmReportShow.sMode = "0" // frmReportShow.CRViewer1.ViewReport // Screen.MousePointer = vbDefault // frmReportShow.Show 1 // Exit Sub //End If Report.Database.Tables(0).SetDataSource(rsRep); //UPGRADE_WARNING: Couldn't resolve default property of object Report.VerifyOnEveryPrint. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"' //Report.VerifyOnEveryPrint = True //frmReportShow.caption = Report.txtTitle.Text My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); System.Windows.Forms.Application.DoEvents(); this.cmdNext.Enabled = false; frmMode[0].Visible = false; return; } else { //if there is no record if (bDCChk == false) { //delete OpenTable cn.Execute("DELETE * FROM OpenTable;"); cn.Execute("DROP TABLE OpenTable;"); //create OpenTable strFldName = "OpenTable_WaitronID Number NOT NULL, "; strFldName = strFldName + "OpenTable_TableID Number NOT NULL, "; strFldName = strFldName + "OpenTable_Text Text(50), "; strFldName = strFldName + "OpenTable_X Number, "; strFldName = strFldName + "OpenTable_Y Number, "; strFldName = strFldName + "OpenTable_Date DateTime, "; strFldName = strFldName + "OpenTable_Complete YesNo, "; strFldName = strFldName + "OpenTable_GuestCount Number, "; strFldName = strFldName + "OpenTable_Delivery YesNo, "; strFldName = strFldName + "OpenTable_TelNumber Text(50), "; strFldName = strFldName + "OpenTable_ID AUTOINCREMENT, "; strFldName = strFldName + "OpenTable_Discount Currency, "; strFldName = strFldName + "OpenTable_DiscountReason Text(50), "; strFldName = strFldName + "OpenTable_VoidReason Text(50), "; strFldName = strFldName + "OpenTable_FLTableID Number, "; strFldName = strFldName + "OpenTable_Printed YesNo"; Debug.Print(strFldName); cn.Execute("CREATE TABLE OpenTable (" + strFldName + ")"); System.Windows.Forms.Application.DoEvents(); } } //Check for open table... rs = modRecordSet.getRS(ref "SELECT DISTINCT TOP 100 PERCENT POS.POSID, POS.POS_Name FROM POS INNER JOIN Sale ON POS.POSID = Sale.Sale_PosID AND POS.POS_DeclarationID = Sale.Sale_DeclarationID Where (POS.POS_Disabled = 0) ORDER BY POS.POS_Name"); this.lstPOS.Items.Clear(); while (!(rs.EOF)) { lstPOS.Items.Add(new LBI(rs.Fields("POS_Name").Value, rs.Fields("POSID").Value)); rs.MoveNext(); } if (lstPOS.Items.Count) { lstPOS.SelectedIndex = -1; lstPOS.Visible = true; this.cmdNext.Enabled = false; } else { doMode(ref mdTransactions); } break; case mdTransactions: errDesc = "mdTransactions Point"; rs = modRecordSet.getRS(ref "SELECT Count(Sale.SaleID) AS CountOfSaleID FROM Sale INNER JOIN Company ON Sale.Sale_DayEndID = Company.Company_DayEndID;"); if (rs.BOF | rs.EOF) { this.cmdNext.Enabled = false; } else { if (rs.Fields(0).Value == 0) { this.cmdNext.Enabled = false; } else { doMode(ref mdConfirm); } } break; case mdConfirm: calDayEnd.SetDate(DateAndTime.Now); if (DateAndTime.Hour(DateAndTime.TimeOfDay) < 12) { calDayEnd.SetDate(System.DateTime.FromOADate(DateAndTime.Today.ToOADate() - 1)); } if (modApplication.bolAutoDE == true) { if (bolAutoDE_Error == false) { doMode(ref mdComplete); } else { this.cmdNext.Enabled = true; } } else { this.cmdNext.Enabled = true; } break; case mdComplete: errDesc = "mdComplete Point"; rsBit = modRecordSet.getRS(ref "SELECT * FROM Company"); gParameters = Convert.ToInt32(0 + rsBit.Fields("Company_DayEndBit").Value); bHOAutoUpload = (Information.IsDBNull(rsBit.Fields("Company_HOLink").Value) ? false : rsBit.Fields("Company_HOLink").Value); if (modApplication.bolAutoDE == true) { } else { if (gParameters & gParPastelReport) { My.MyProject.Forms.frmMenu.Automaticload(); modBResolutions.blpastel = true; modApplication.report_VATPASTEL(); modApplication.ExportToCSVFile(); } } rs = modRecordSet.getRS(ref "SELECT DayEnd_Date FROM DayEnd ORDER BY DayEnd_Date DESC;"); if (rs.RecordCount > 1) { rs.MoveNext(); if (DateAndTime.DateSerial(DateAndTime.Year(calDayEnd.SelectionStart), DateAndTime.Month(calDayEnd.SelectionStart), DateAndTime.Day(calDayEnd.SelectionStart)) <= rs.Fields(0).Value) { Interaction.MsgBox("You may not do a day end run for a previous day!" + Constants.vbCrLf + Constants.vbCrLf + "The last day end run was on the " + Strings.Format(rs.Fields(0).Value, "ddd, dd-mmm-yyyy") + "!", MsgBoxStyle.Exclamation, "DAYEND RUN"); bolAutoDE_Error = true; doMode(ref mdConfirm); return; } } this.Cursor = System.Windows.Forms.Cursors.WaitCursor; modApplication.modUpdate = 1; modApplication.updateStockMovement(); errDesc = "After updateStockMovement Point"; //Multi Warehouse change cnnDB.Execute "UPDATE WarehouseStockItemLnk INNER JOIN (Company INNER JOIN DayEndStockItemLnk ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID) ON WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID SET WarehouseStockItemLnk.WarehouseStockItemLnk_Quantity = [DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]-[DayEndStockItemLnk_QuantityShrink]+[DayEndStockItemLnk_QuantityGRV] WHERE (((WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)=2) AND (([DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]+[DayEndStockItemLnk_QuantityShrink]-[DayEndStockItemLnk_QuantityGRV])<>[WarehouseStockItemLnk_Quantity]));" //Tranfer change cnnDB.Execute "UPDATE WarehouseStockItemLnk INNER JOIN (Company INNER JOIN DayEndStockItemLnk ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID) ON WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID AND WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID = DayEndStockItemLnk.DayEndStockItemLnk_Warehouse SET WarehouseStockItemLnk.WarehouseStockItemLnk_Quantity = [DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]-[DayEndStockItemLnk_QuantityShrink]+[DayEndStockItemLnk_QuantityGRV] WHERE (((WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)=2) AND (([DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]+[DayEndStockItemLnk_QuantityShrink]-[DayEndStockItemLnk_QuantityGRV])<>[WarehouseStockItemLnk_Quantity]));" modRecordSet.cnnDB.Execute("UPDATE WarehouseStockItemLnk INNER JOIN (Company INNER JOIN DayEndStockItemLnk ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID) ON WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID AND WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID = DayEndStockItemLnk.DayEndStockItemLnk_Warehouse SET WarehouseStockItemLnk.WarehouseStockItemLnk_Quantity = [DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]-[DayEndStockItemLnk_QuantityShrink]+[DayEndStockItemLnk_QuantityGRV]+[DayEndStockItemLnk_QuantityTransafer] WHERE (((WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)=2) AND (([DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]+[DayEndStockItemLnk_QuantityShrink]-[DayEndStockItemLnk_QuantityGRV]-[DayEndStockItemLnk_QuantityTransafer])<>[WarehouseStockItemLnk_Quantity]));"); sql = "INSERT INTO StockitemHistory ( StockitemHistory_StockItemID, StockitemHistory_Value, StockitemHistory_Day1, StockitemHistory_Day2, StockitemHistory_Day3, StockitemHistory_Day4, StockitemHistory_Day5, StockitemHistory_Day6, StockitemHistory_Day7, StockitemHistory_Day8, StockitemHistory_Day9, StockitemHistory_Day10, StockitemHistory_Day11, StockitemHistory_Day12, StockitemHistory_Week1, StockitemHistory_Week2, StockitemHistory_Week3, StockitemHistory_Week4, StockitemHistory_Week5, StockitemHistory_Week6, StockitemHistory_Week7, StockitemHistory_Week8, StockitemHistory_Week9, StockitemHistory_Week11, StockitemHistory_Month1, StockitemHistory_Month2, StockitemHistory_Month3, StockitemHistory_Month4, StockitemHistory_Month5, StockitemHistory_Month6, StockitemHistory_Month7, StockitemHistory_Month8, StockitemHistory_Month9, StockitemHistory_Month10, StockitemHistory_Month11, StockitemHistory_Month12 ) "; sql = sql + "SELECT StockItem.StockItemID, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS Expr5, 0 AS Expr6, 0 AS Expr7, 0 AS Expr8, 0 AS Expr9, 0 AS Expr10, 0 AS Expr11, 0 AS Expr12, 0 AS Expr13, 0 AS Expr14, 0 AS Expr15, 0 AS Expr16, 0 AS Expr17, 0 AS Expr18, 0 AS Expr19, 0 AS Expr20, 0 AS Expr21, 0 AS Expr22, 0 AS Expr23, 0 AS Expr24, 0 AS Expr25, 0 AS Expr26, 0 AS Expr27, 0 AS Expr28, 0 AS Expr29, 0 AS Expr30, 0 AS Expr31, 0 AS Expr32, 0 AS Expr33, 0 AS Expr34, 0 AS Expr35, 0 AS Expr1 FROM StockItem LEFT JOIN StockitemHistory ON StockItem.StockItemID = StockitemHistory.StockitemHistory_StockItemID WHERE (((StockitemHistory.StockitemHistory_StockItemID) Is Null));"; modRecordSet.cnnDB.Execute(sql); //add temporary table for StockitemHistory ChkStockitemHistoryTable(); System.Windows.Forms.Application.DoEvents(); sql = "INSERT INTO StockitemHistory777 ( StockItemID, QuantitySales ) "; sql = sql + "SELECT DayEndStockItemLnk.DayEndStockItemLnk_StockItemID, Sum(DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales) AS Exp1 FROM Company INNER JOIN DayEndStockItemLnk ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID GROUP BY DayEndStockItemLnk.DayEndStockItemLnk_StockItemID;"; modRecordSet.cnnDB.Execute(sql); //add temporary table for StockitemHistory System.DateTime dated = default(System.DateTime); dated = calDayEnd.SelectionRange.Start; //cnnDB.Execute "UPDATE Company INNER JOIN (StockitemHistory INNER JOIN DayEndStockItemLnk ON StockitemHistory.StockitemHistory_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID) ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID SET StockitemHistory.StockitemHistory_Day1 = [DayEndStockItemLnk]![DayEndStockItemLnk_QuantitySales];" modRecordSet.cnnDB.Execute("UPDATE StockitemHistory INNER JOIN StockitemHistory777 ON StockitemHistory.StockitemHistory_StockItemID = StockitemHistory777.StockItemID SET StockitemHistory.StockitemHistory_Day1 = [StockitemHistory777]![QuantitySales];"); //cnnDB.Execute "UPDATE Company INNER JOIN (StockitemHistory INNER JOIN DayEndStockItemLnk ON StockitemHistory.StockitemHistory_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID) ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID SET StockitemHistory.StockitemHistory_Week1 = [StockitemHistory]![StockitemHistory_Week1]+[DayEndStockItemLnk]![DayEndStockItemLnk_QuantitySales];" modRecordSet.cnnDB.Execute("UPDATE StockitemHistory INNER JOIN StockitemHistory777 ON StockitemHistory.StockitemHistory_StockItemID = StockitemHistory777.StockItemID SET StockitemHistory.StockitemHistory_Week1 = [StockitemHistory]![StockitemHistory_Week1]+[StockitemHistory777]![QuantitySales];"); //cnnDB.Execute "UPDATE Company INNER JOIN (StockitemHistory INNER JOIN DayEndStockItemLnk ON StockitemHistory.StockitemHistory_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID) ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID SET StockitemHistory.StockitemHistory_Month1 = [StockitemHistory]![StockitemHistory_Month1]+[DayEndStockItemLnk]![DayEndStockItemLnk_QuantitySales];" modRecordSet.cnnDB.Execute("UPDATE StockitemHistory INNER JOIN StockitemHistory777 ON StockitemHistory.StockitemHistory_StockItemID = StockitemHistory777.StockItemID SET StockitemHistory.StockitemHistory_Month1 = [StockitemHistory]![StockitemHistory_Month1]+[StockitemHistory777]![QuantitySales];"); sql = "UPDATE StockitemHistory SET StockitemHistory.StockitemHistory_Day12 = [StockitemHistory]![StockitemHistory_Day11], StockitemHistory.StockitemHistory_Day11 = [StockitemHistory]![StockitemHistory_Day10], StockitemHistory.StockitemHistory_Day10 = [StockitemHistory]![StockitemHistory_Day9], StockitemHistory.StockitemHistory_Day9 = [StockitemHistory]![StockitemHistory_Day8], StockitemHistory.StockitemHistory_Day8 = [StockitemHistory]![StockitemHistory_Day7], StockitemHistory.StockitemHistory_Day7 = [StockitemHistory]![StockitemHistory_Day6], StockitemHistory.StockitemHistory_Day6 = [StockitemHistory]![StockitemHistory_Day5], StockitemHistory.StockitemHistory_Day5 = [StockitemHistory]![StockitemHistory_Day4], "; sql = sql + "StockitemHistory.StockitemHistory_Day4 = [StockitemHistory]![StockitemHistory_Day3], StockitemHistory.StockitemHistory_Day3 = [StockitemHistory]![StockitemHistory_Day2], StockitemHistory.StockitemHistory_Day2 = [StockitemHistory]![StockitemHistory_Day1], StockitemHistory.StockitemHistory_Day1 = 0;"; modRecordSet.cnnDB.Execute(sql); modRecordSet.cnnDB.Execute("UPDATE (Company INNER JOIN DayEndStockItemLnk ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID) INNER JOIN StockItem ON DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = StockItem.StockItemID SET DayEndStockItemLnk.DayEndStockItemLnk_ListCost = [StockItem]![StockItem_ListCost]/[StockItem]![StockItem_Quantity], DayEndStockItemLnk.DayEndStockItemLnk_ActualCost = [StockItem]![StockItem_ActualCost]/[StockItem]![StockItem_Quantity];"); modRecordSet.cnnDB.Execute("INSERT INTO DayEnd ( DayEndID, DayEnd_MonthEndID, DayEnd_Date ) SELECT Company.Company_DayEndID, Company.Company_MonthEndID, #" + dated.Month + "# FROM Company LEFT JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID WHERE (((DayEnd.DayEndID) Is Null));"); modRecordSet.cnnDB.Execute("UPDATE Company INNER JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID SET DayEnd.DayEnd_MonthEndID = [Company]![Company_MonthEndID], DayEnd.DayEnd_Date = #" + dated.Day + "#;"); modRecordSet.cnnDB.Execute("UPDATE Company SET Company.Company_DayEndID = [Company]![Company_DayEndID]+1;"); if (Strings.LCase(Strings.Format(this.calDayEnd.SelectionRange.Start.Day + 1, "ddd")) == "sun") { //If LCase(Format(System.DateTime.FromOADate(dated.ToOADate + 1)), "ddd", 1, FirstWeekOfYear.FirstFullWeek) = "sun" Then modRecordSet.cnnDB.Execute("INSERT INTO DayEnd ( DayEndID, DayEnd_MonthEndID, DayEnd_Date ) SELECT Company.Company_DayEndID, Company.Company_MonthEndID, #" + System.DateTime.FromOADate(dated.ToOADate() + 1) + "# FROM Company LEFT JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID WHERE (((DayEnd.DayEndID) Is Null));"); sql = "UPDATE StockitemHistory SET StockitemHistory.StockitemHistory_Week12 = [StockitemHistory]![StockitemHistory_Week11], StockitemHistory.StockitemHistory_Week11 = [StockitemHistory]![StockitemHistory_Week10], StockitemHistory.StockitemHistory_Week10 = [StockitemHistory]![StockitemHistory_Week9], StockitemHistory.StockitemHistory_Week9 = [StockitemHistory]![StockitemHistory_Week8], StockitemHistory.StockitemHistory_Week8 = [StockitemHistory]![StockitemHistory_Week7], StockitemHistory.StockitemHistory_Week7 = [StockitemHistory]![StockitemHistory_Week6], StockitemHistory.StockitemHistory_Week6 = [StockitemHistory]![StockitemHistory_Week5], StockitemHistory.StockitemHistory_Week5 = [StockitemHistory]![StockitemHistory_Week4], "; sql = sql + "StockitemHistory.StockitemHistory_Week4 = [StockitemHistory]![StockitemHistory_Week3], StockitemHistory.StockitemHistory_Week3 = [StockitemHistory]![StockitemHistory_Week2], StockitemHistory.StockitemHistory_Week2 = [StockitemHistory]![StockitemHistory_Week1], StockitemHistory.StockitemHistory_Week1 = 0;"; modRecordSet.cnnDB.Execute(sql); } else { modRecordSet.cnnDB.Execute("INSERT INTO DayEnd ( DayEndID, DayEnd_MonthEndID, DayEnd_Date ) SELECT Company.Company_DayEndID, Company.Company_MonthEndID, #" + System.DateTime.FromOADate(dated.ToOADate() + 1) + "# FROM Company LEFT JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID WHERE (((DayEnd.DayEndID) Is Null));"); } //delete from dayendstocklink AND dayend depositlink for that dayend rs = modRecordSet.getRS(ref "SELECT Company_DayEndID FROM Company;"); if (rs.RecordCount > 1) { modRecordSet.cnnDB.Execute("DELETE * FROM DayEndStockItemLnk WHERE (((DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)=" + rs.Fields("Company_DayEndID").Value + "));"); modRecordSet.cnnDB.Execute("DELETE * FROM DayEndDepositItemLnk WHERE (((DayEndDepositItemLnk.DayEndDepositItemLnk_DayEndID)=" + rs.Fields("Company_DayEndID").Value + "));"); } //On Local Error Resume Next //Multi Warehouse change cnnDB.Execute "INSERT INTO DayEndStockItemLnk ( DayEndStockItemLnk_DayEndID, DayEndStockItemLnk_StockItemID, DayEndStockItemLnk_Quantity, DayEndStockItemLnk_QuantitySales, DayEndStockItemLnk_QuantityShrink, DayEndStockItemLnk_QuantityGRV, DayEndStockItemLnk_ListCost, DayEndStockItemLnk_ActualCost ) SELECT Company.Company_DayEndID, StockItem.StockItemID, 0 AS Expr1, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, [StockItem]![StockItem_ListCost]/[StockItem]![StockItem_Quantity], [StockItem]![StockItem_ActualCost]/[StockItem]![StockItem_Quantity] FROM Company, StockItem;" modRecordSet.cnnDB.Execute("INSERT INTO DayEndStockItemLnk ( DayEndStockItemLnk_DayEndID, DayEndStockItemLnk_StockItemID, DayEndStockItemLnk_Quantity, DayEndStockItemLnk_QuantitySales, DayEndStockItemLnk_QuantityShrink, DayEndStockItemLnk_QuantityGRV, DayEndStockItemLnk_ListCost, DayEndStockItemLnk_ActualCost, DayEndStockItemLnk_Warehouse ) SELECT Company.Company_DayEndID, StockItem.StockItemID, 0 AS Expr1, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, [StockItem]![StockItem_ListCost]/[StockItem]![StockItem_Quantity], [StockItem]![StockItem_ActualCost]/[StockItem]![StockItem_Quantity], Warehouse.WarehouseID FROM Company, StockItem, Warehouse;"); //On Local Error GoTo 0 //Multi Warehouse change cnnDB.Execute "UPDATE WarehouseStockItemLnk INNER JOIN (DayEndStockItemLnk INNER JOIN Company ON DayEndStockItemLnk.DayEndStockItemLnk_DayEndID = Company.Company_DayEndID) ON WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID SET DayEndStockItemLnk.DayEndStockItemLnk_Quantity = [WarehouseStockItemLnk]![WarehouseStockItemLnk_Quantity] WHERE (((WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)=1 Or (WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)=2));" modRecordSet.cnnDB.Execute("UPDATE WarehouseStockItemLnk INNER JOIN (DayEndStockItemLnk INNER JOIN Company ON DayEndStockItemLnk.DayEndStockItemLnk_DayEndID = Company.Company_DayEndID) ON WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID AND WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID = DayEndStockItemLnk.DayEndStockItemLnk_Warehouse SET DayEndStockItemLnk.DayEndStockItemLnk_Quantity = [WarehouseStockItemLnk]![WarehouseStockItemLnk_Quantity] WHERE (((WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)=1 Or (WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)=2));"); sql = "INSERT INTO DayEndDepositItemLnk ( DayEndDepositItemLnk_DayEndID, DayEndDeposittemLnk_DepositID, DayEndDeposittemLnk_DepositType, DayEndDepositItemLnk_Quantity, DayEndDepositItemLnk_QuantitySales, DayEndDepositItemLnk_QuantityShrink, DayEndDepositItemLnk_QuantityGRV, DayEndDepositItemLnk_UnitCost, DayEndDepositItemLnk_CaseCost, DayEndDepositItemLnk_CaseQuantity ) SELECT DISPLAY_DepositDayEnd.Company_DayEndID, DISPLAY_DepositDayEnd.DepositID, DISPLAY_DepositDayEnd.type, 0, 0, 0, 0, Deposit.Deposit_UnitCost, Deposit.Deposit_CaseCost, Deposit.Deposit_Quantity FROM DayEndDepositItemLnk RIGHT JOIN (Deposit INNER JOIN DISPLAY_DepositDayEnd ON Deposit.DepositID = DISPLAY_DepositDayEnd.DepositID) ON (DayEndDepositItemLnk.DayEndDeposittemLnk_DepositType = DISPLAY_DepositDayEnd.type) AND (DayEndDepositItemLnk.DayEndDeposittemLnk_DepositID = DISPLAY_DepositDayEnd.DepositID) AND (DayEndDepositItemLnk.DayEndDepositItemLnk_DayEndID = DISPLAY_DepositDayEnd.Company_DayEndID) "; sql = sql + "WHERE (((DayEndDepositItemLnk.DayEndDepositItemLnk_DayEndID) Is Null));"; modRecordSet.cnnDB.Execute(sql); errDesc = "Middle Point"; rs = modRecordSet.getRS(ref "SELECT [Company_DayEndID]-1 AS theDayend FROM Company;"); rs = modRecordSet.getRS(ref "SELECT Min(DayEnd.DayEndID) AS minDay, Max(DayEnd_1.DayEndID) AS MaxDay FROM (Company INNER JOIN DayEnd AS DayEnd_1 ON Company.Company_MonthEndID = DayEnd_1.DayEnd_MonthEndID) INNER JOIN DayEnd ON Company.Company_MonthEndID = DayEnd.DayEnd_MonthEndID;"); dayStart = rs.Fields("minDay").Value; dayEnd = rs.Fields("MaxDay").Value; rs.Close(); modRecordSet.cnnDB.Execute("UPDATE DayEndStockItemLnk SET DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV = 0;"); modRecordSet.cnnDB.Execute("UPDATE (DayEndStockItemLnk INNER JOIN GRVItem ON DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = GRVItem.GRVItem_StockItemID) INNER JOIN GRV ON (DayEndStockItemLnk.DayEndStockItemLnk_DayEndID = GRV.GRV_DayEndID) AND (GRVItem.GRVItem_GRVID = GRV.GRVID) SET DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV = [DayEndStockItemLnk_QuantityGRV]+IIf([GRVItem]![GRVItem_Return],0-[GRVItem_PackSize]*[GRVItem_Quantity],[GRVItem_PackSize]*[GRVItem_Quantity]) WHERE (((DayEndStockItemLnk.DayEndStockItemLnk_Warehouse)=2) AND ((GRV.GRV_GRVStatusID)=3));"); for (x = dayStart; x <= dayEnd; x++) { //Multi Warehouse change cnnDB.Execute "UPDATE DayEndStockItemLnk AS dayEndToday INNER JOIN DayEndStockItemLnk AS Tomarrow ON dayEndToday.DayEndStockItemLnk_StockItemID = Tomarrow.DayEndStockItemLnk_StockItemID SET Tomarrow.DayEndStockItemLnk_Quantity = [dayEndToday]![DayEndStockItemLnk_Quantity]-[dayEndToday]![DayEndStockItemLnk_QuantitySales]-[dayEndToday]![DayEndStockItemLnk_QuantityShrink]+[dayEndToday]![DayEndStockItemLnk_QuantityGRV] WHERE (((dayEndToday.DayEndStockItemLnk_DayEndID)=" & x & ") AND ((Tomarrow.DayEndStockItemLnk_DayEndID)=[dayendtoday]![DayEndStockItemLnk_DayEndID]+1));" //Tranfer change cnnDB.Execute "UPDATE DayEndStockItemLnk AS dayEndToday INNER JOIN DayEndStockItemLnk AS Tomarrow ON dayEndToday.DayEndStockItemLnk_StockItemID = Tomarrow.DayEndStockItemLnk_StockItemID AND dayEndToday.DayEndStockItemLnk_Warehouse = Tomarrow.DayEndStockItemLnk_Warehouse SET Tomarrow.DayEndStockItemLnk_Quantity = [dayEndToday]![DayEndStockItemLnk_Quantity]-[dayEndToday]![DayEndStockItemLnk_QuantitySales]-[dayEndToday]![DayEndStockItemLnk_QuantityShrink]+[dayEndToday]![DayEndStockItemLnk_QuantityGRV] WHERE (((dayEndToday.DayEndStockItemLnk_DayEndID)=" & x & ") AND ((Tomarrow.DayEndStockItemLnk_DayEndID)=[dayendtoday]![DayEndStockItemLnk_DayEndID]+1));" modRecordSet.cnnDB.Execute("UPDATE DayEndStockItemLnk AS dayEndToday INNER JOIN DayEndStockItemLnk AS Tomarrow ON dayEndToday.DayEndStockItemLnk_StockItemID = Tomarrow.DayEndStockItemLnk_StockItemID AND dayEndToday.DayEndStockItemLnk_Warehouse = Tomarrow.DayEndStockItemLnk_Warehouse SET Tomarrow.DayEndStockItemLnk_Quantity = [dayEndToday]![DayEndStockItemLnk_Quantity]-[dayEndToday]![DayEndStockItemLnk_QuantitySales]-[dayEndToday]![DayEndStockItemLnk_QuantityShrink]+[dayEndToday]![DayEndStockItemLnk_QuantityGRV]+[dayEndToday]![DayEndStockItemLnk_QuantityTransafer] WHERE (((dayEndToday.DayEndStockItemLnk_DayEndID)=" + x + ") AND ((Tomarrow.DayEndStockItemLnk_DayEndID)=[dayendtoday]![DayEndStockItemLnk_DayEndID]+1));"); } //Multi Warehouse change cnnDB.Execute "UPDATE WarehouseStockItemLnk INNER JOIN (Company INNER JOIN DayEndStockItemLnk ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID) ON WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID SET WarehouseStockItemLnk.WarehouseStockItemLnk_Quantity = [DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]-[DayEndStockItemLnk_QuantityShrink]+[DayEndStockItemLnk_QuantityGRV] WHERE (((WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)=2) AND (([DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]+[DayEndStockItemLnk_QuantityShrink]-[DayEndStockItemLnk_QuantityGRV])<>[WarehouseStockItemLnk_Quantity]));" //Tranfer change cnnDB.Execute "UPDATE WarehouseStockItemLnk INNER JOIN (Company INNER JOIN DayEndStockItemLnk ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID) ON WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID AND WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID = DayEndStockItemLnk.DayEndStockItemLnk_Warehouse SET WarehouseStockItemLnk.WarehouseStockItemLnk_Quantity = [DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]-[DayEndStockItemLnk_QuantityShrink]+[DayEndStockItemLnk_QuantityGRV] WHERE (((WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)=2) AND (([DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]+[DayEndStockItemLnk_QuantityShrink]-[DayEndStockItemLnk_QuantityGRV])<>[WarehouseStockItemLnk_Quantity]));" modRecordSet.cnnDB.Execute("UPDATE WarehouseStockItemLnk INNER JOIN (Company INNER JOIN DayEndStockItemLnk ON Company.Company_DayEndID = DayEndStockItemLnk.DayEndStockItemLnk_DayEndID) ON WarehouseStockItemLnk.WarehouseStockItemLnk_StockItemID = DayEndStockItemLnk.DayEndStockItemLnk_StockItemID AND WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID = DayEndStockItemLnk.DayEndStockItemLnk_Warehouse SET WarehouseStockItemLnk.WarehouseStockItemLnk_Quantity = [DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]-[DayEndStockItemLnk_QuantityShrink]+[DayEndStockItemLnk_QuantityGRV]+[DayEndStockItemLnk_QuantityTransafer] WHERE (((WarehouseStockItemLnk.WarehouseStockItemLnk_WarehouseID)>1) AND (([DayEndStockItemLnk_Quantity]-[DayEndStockItemLnk_QuantitySales]+[DayEndStockItemLnk_QuantityShrink]-[DayEndStockItemLnk_QuantityGRV]-[DayEndStockItemLnk_QuantityTransafer])<>[WarehouseStockItemLnk_Quantity]));"); modRecordSet.cnnDB.Execute("UPDATE (DayEndDepositItemLnk AS DayEndDepositItemLnk_1 INNER JOIN Company ON DayEndDepositItemLnk_1.DayEndDepositItemLnk_DayEndID = Company.Company_DayEndID) INNER JOIN DayEndDepositItemLnk ON (DayEndDepositItemLnk_1.DayEndDeposittemLnk_DepositID = DayEndDepositItemLnk.DayEndDeposittemLnk_DepositID) AND (DayEndDepositItemLnk_1.DayEndDeposittemLnk_DepositType = DayEndDepositItemLnk.DayEndDeposittemLnk_DepositType) SET DayEndDepositItemLnk_1.DayEndDepositItemLnk_Quantity = [DayEndDepositItemLnk!DayEndDepositItemLnk_Quantity]+[DayEndDepositItemLnk!DayEndDepositItemLnk_QuantityShrink]-[DayEndDepositItemLnk!DayEndDepositItemLnk_QuantitySales]+[DayEndDepositItemLnk!DayEndDepositItemLnk_QuantityGRV] WHERE (((DayEndDepositItemLnk.DayEndDepositItemLnk_DayEndID)=[Company_DayEndID]-1));"); //clear Make finish check for sale qty modRecordSet.cnnDB.Execute("UPDATE StockItem SET StockItem.StockItem_MakeFinishItem = 0 WHERE StockItemID > 0;"); if (modApplication.bolAutoDE == true) { } else { modApplication.loadDayEndReport(dayEnd - 1); } this.cmdNext.Visible = false; System.Windows.Forms.Application.DoEvents(); //blMontheEnd = False errDesc = "End Point"; //On Local Error Resume Next 'Prevent crushes for month End if (gParameters & gCopySalesToHQ) { //Do Collect Sales.... modEmails.CollectSalesHQ(); } else { //autoupload report //get HO info rsHO = modRecordSet.getRS(ref "Select Comp_ID FROM CompanyEmails;"); if (rsHO.RecordCount > 0) { //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(rsHO.Fields("Comp_ID").Value)) { } else { if (rsHO.Fields("Comp_ID").Value == 0) { if (bHOAutoUpload) { cmdHO_Click(); } } } } } System.Windows.Forms.Application.DoEvents(); cmdPrintSlip_Click(); System.Windows.Forms.Application.DoEvents(); if (gParameters & gZeroStock_DayEnd) { System.Windows.Forms.Application.DoEvents(); Label3.Text = "Please Wait, Reseting Stock to Zero..."; Label3.Visible = true; modApplication.EmulateSnapShot(); System.Windows.Forms.Application.DoEvents(); System.Windows.Forms.Application.DoEvents(); Label3.Text = "Reseting Stock Complete..."; } this.Cursor = System.Windows.Forms.Cursors.Default; if (DateAndTime.Day(System.DateTime.FromOADate(calDayEnd.SelectionStart.ToOADate() + 1)) == 1) { switch (Interaction.MsgBox("This is the last day of the month!" + Constants.vbCrLf + Constants.vbCrLf + "Do you want to do the Month End Now?", MsgBoxStyle.Question + MsgBoxStyle.YesNoCancel, "Month End Run")) { case MsgBoxResult.Yes: //blMontheEnd = True My.MyProject.Forms.frmMonthEnd.ShowDialog(); break; } } if (modApplication.bolAutoDE == true) { lString = modRecordSet.serverPath + "data\\4POSInterface\\4POSAUTODEDONE.txt"; if (fso.FileExists(lString)) fso.DeleteFile(lString, true); lTextstream = fso.OpenTextFile(lString, Scripting.IOMode.ForWriting, true); lTextstream.Write("DONE"); lTextstream.Close(); System.Environment.Exit(0); //Unload Me } break; } return; ErrHandlerr: Interaction.MsgBox("Error while DayEnd on " + errDesc + " : " + Err().Number + " : " + Err().Description + " : " + Err().Source); // ERROR: Not supported in C#: ResumeStatement }
public bool GLvouchersimport(string usertoken, string dbconn, DataSet dsimportedvouchers, string userid, out int importsuccessrows, out int importfailurerows, out DataSet dsreturnvouchers, out string errmsg) { string strSql = "", strTempTable = "tempdb.dbo.bestu8cus_gl_accvouchers"; int v_importsuccessrows = 0, v_importfailurerows = 0; System.Object rsaffected = new System.Object(); //创建或清除凭证导入临时表数据 #region ADODB.Recordset rs = new ADODB.Recordset(); ADODB.Connection conn = new ADODB.Connection(); conn.Open(dbconn); strSql = "SELECT count(*) FROM tempdb.dbo.sysobjects WHERE name = 'bestu8cus_gl_accvouchers'"; rs = conn.Execute(strSql, out rsaffected, -1); if (Convert.ToInt16(rs.Fields[0].Value) > 0) { strSql = "DELETE FROM " + strTempTable; rs = conn.Execute(strSql, out rsaffected, -1); } else { strSql = "CREATE TABLE " + strTempTable; strSql = strSql + "( csign NVARCHAR (28),"; //凭证类别字 strSql = strSql + "ino_id SMALLINT,"; //凭证编号 strSql = strSql + "inid SMALLINT,"; //行号 strSql = strSql + "cbill NVARCHAR (80),"; //制单人 strSql = strSql + "doutbilldate DATETIME,"; //外部凭证制单日期 strSql = strSql + "ccashier NVARCHAR (80),"; //出纳签字人 strSql = strSql + "idoc SMALLINT DEFAULT 0,"; //附单据数 strSql = strSql + "ctext1 NVARCHAR (50),"; //凭证头自定义项1 strSql = strSql + "ctext2 NVARCHAR (50),"; //凭证头自定义项2 strSql = strSql + "cexch_name NVARCHAR (28),"; //币种名称 strSql = strSql + "cdigest NVARCHAR (120),"; //凭证摘要 strSql = strSql + "ccode NVARCHAR (40),"; //科目编码 strSql = strSql + "md MONEY DEFAULT 0,"; //借方金额 strSql = strSql + "mc MONEY DEFAULT 0,"; //贷方金额 strSql = strSql + "md_f MONEY DEFAULT 0,"; //外币借方金额 strSql = strSql + "mc_f MONEY DEFAULT 0,"; //外币贷方金额 strSql = strSql + "nfrat FLOAT DEFAULT 0,"; //汇率 strSql = strSql + "nd_s FLOAT DEFAULT 0,"; //数量借方 strSql = strSql + "nc_s FLOAT DEFAULT 0,"; //数量贷方 strSql = strSql + "csettle NVARCHAR (23),"; //结算方式编码 strSql = strSql + "cn_id NVARCHAR (30),"; //票据号 strSql = strSql + "dt_date DATETIME,"; //票号发生日期 strSql = strSql + "cdept_id NVARCHAR (12),"; //部门编码 strSql = strSql + "cperson_id NVARCHAR (80),"; //职员编码 strSql = strSql + "ccus_id NVARCHAR (80),"; //客户编码 strSql = strSql + "csup_id NVARCHAR (20),"; //供应商编码 strSql = strSql + "citem_id NVARCHAR (80),"; //物料编码 strSql = strSql + "citem_class NVARCHAR (22),"; //物料大类编码 strSql = strSql + "cname NVARCHAR (40),"; //业务员 strSql = strSql + "ccode_equal NVARCHAR (50),"; //对方科目编码 strSql = strSql + "bvouchedit BIT DEFAULT 0,"; //凭证是否可修改 strSql = strSql + "bvouchaddordele BIT DEFAULT 0,"; //凭证分录是否可增删 strSql = strSql + "bvouchmoneyhold BIT DEFAULT 0,"; //凭证合计金额是否保值 strSql = strSql + "bvalueedit BIT DEFAULT 0,"; //分录数值是否可修改 strSql = strSql + "bcodeedit BIT DEFAULT 0,"; //分录科目是否可修改 strSql = strSql + "ccodecontrol NVARCHAR (50),"; //分录受控科目可用状态 strSql = strSql + "bPCSedit BIT DEFAULT 0,"; //分录来往项是否可修改 strSql = strSql + "bDeptedit BIT DEFAULT 0,"; //分录部门是否可修改 strSql = strSql + "bItemedit BIT DEFAULT 0,"; //分录物料是否可修改 strSql = strSql + "bCusSupInput BIT DEFAULT 0,"; //分录往来项是否必须输入 strSql = strSql + "coutaccset NVARCHAR (23),"; //外部凭证账套号 strSql = strSql + "ioutyear SMALLINT,"; //外部凭证会计年度 strSql = strSql + "coutsysname NVARCHAR (50) NOT NULL,"; //外部凭证系统名称 这里如果不放GL 则外部导入的凭证无法修改。 strSql = strSql + "coutsysver NVARCHAR (50),"; //外部凭证系统版本号 strSql = strSql + "ioutperiod TINYINT NOT NULL,"; //外部凭证会计期间 strSql = strSql + "coutsign NVARCHAR (80) NOT NULL,"; //外部凭证业务类型 strSql = strSql + "coutno_id NVARCHAR (100) NOT NULL,"; //外部凭证业务号 (相同的话表示为一张凭证) strSql = strSql + "doutdate DATETIME,"; //外部凭证单据日期 strSql = strSql + "coutbillsign NVARCHAR (80),"; //外部凭证单据类型 strSql = strSql + "coutid NVARCHAR (50),"; //外部凭证单据号 strSql = strSql + "iflag TINYINT,"; //凭证标志 strSql = strSql + "iBG_ControlResult SMALLINT NULL,"; // strSql = strSql + "daudit_date DATETIME NULL,"; // strSql = strSql + "cblueoutno_id NVARCHAR (50) NULL,"; // strSql = strSql + "bWH_BgFlag BIT,"; // strSql = strSql + "cDefine1 NVARCHAR (40),"; //自定义项1 strSql = strSql + "cDefine2 NVARCHAR (40),"; //自定义项2 strSql = strSql + "cDefine3 NVARCHAR (40),"; strSql = strSql + "cDefine4 DATETIME,"; strSql = strSql + "cDefine5 INT,"; strSql = strSql + "cDefine6 DATETIME,"; strSql = strSql + "cDefine7 FLOAT,"; strSql = strSql + "cDefine8 NVARCHAR (4),"; strSql = strSql + "cDefine9 NVARCHAR (8),"; strSql = strSql + "cDefine10 NVARCHAR (60),"; strSql = strSql + "cDefine11 NVARCHAR (120),"; strSql = strSql + "cDefine12 NVARCHAR (120),"; strSql = strSql + "cDefine13 NVARCHAR (120),"; strSql = strSql + "cDefine14 NVARCHAR (120),"; strSql = strSql + "cDefine15 INT,"; strSql = strSql + "cDefine16 FLOAT )"; rs = conn.Execute(strSql, out rsaffected, -1); } #endregion //调用API保存总账凭证 CVoucher.CVInterface glcvoucher = new CVoucher.CVInterface(); glcvoucher.set_Connection(conn); glcvoucher.strTempTable = strTempTable; glcvoucher.LoginByUserToken(usertoken); //根据dataset中导入数据分组循环导入U8系统 dsreturnvouchers = dsimportedvouchers.Clone(); //添加对账套的校验逻辑 System.Data.DataTable dtdistinctaccid = dsimportedvouchers.Tables["GLVouchers"].DefaultView.ToTable(true, new string[] { "账套" }); if (dtdistinctaccid.Rows.Count > 1) { //返回数据导入是否成功标志 importsuccessrows = 0; importfailurerows = 0; conn.Close(); errmsg = "存在账套不唯一,请核查模板账套列数据"; return(false); } else { if (dtdistinctaccid.Rows[0]["账套"].ToString() != Pubvar.accid) { //返回数据导入是否成功标志 importsuccessrows = 0; importfailurerows = 0; conn.Close(); errmsg = "导入模板账套(" + dtdistinctaccid.Rows[0]["账套"].ToString() + ")与用户登陆U8账套(" + Pubvar.accid + ") 不一致,请核查!"; return(false); } } System.Data.DataTable dtdistinct = dsimportedvouchers.Tables["GLVouchers"].DefaultView.ToTable(true, new string[] { "凭证ID" }); string vougroupby = ""; //设置progressbar步长并显示百分比 importdataprogressBar.Minimum = 0; // 设置进度条最小值. importdataprogressBar.Value = 1; // 设置进度条初始值 importdataprogressBar.Step = 1; // 设置每次增加的步长 importdataprogressBar.Maximum = dtdistinct.Rows.Count; // 设置进度条最大值. //Graphics g = this.importdataprogressBar.CreateGraphics(); for (int i = 0; i < dtdistinct.Rows.Count; i++) { vougroupby = dtdistinct.Rows[i]["凭证ID"].ToString(); string filterestr = ""; //当凭证ID为空或""时的特殊处理 if (!string.IsNullOrEmpty(vougroupby)) { filterestr = "凭证ID = " + "'" + vougroupby + "'"; } else { filterestr = "凭证ID IS NULL OR 凭证ID ='" + "'"; } DataRow[] drgroupby = dsimportedvouchers.Tables["GLVouchers"].Select(filterestr); if ((!string.IsNullOrEmpty(drgroupby[0]["凭证号"].ToString())) || ((!string.IsNullOrEmpty(drgroupby[0]["是否导入"].ToString())) && (drgroupby[0]["是否导入"].ToString() == "N")) || (string.IsNullOrEmpty(drgroupby[0]["凭证ID"].ToString()))) { //复制已成功导入的数据到返回数据表中 for (int k = 0; k < drgroupby.Count(); k++) { dsreturnvouchers.Tables["GLVouchers"].ImportRow(drgroupby[k]); } } else { for (int j = 0; j < drgroupby.Count(); j++) { strSql = "INSERT INTO " + strTempTable + "(ioutperiod,coutsign ,cSign,coutno_id,cdigest,ctext1,coutsysname,cbill,inid,ccode,cexch_name ,doutbilldate,bvouchedit,bvouchaddordele,bvouchmoneyhold,bvalueedit,bcodeedit,md_f,mc_f,md,mc,nfrat,cdept_id,cperson_id,ccus_id,csup_id,citem_class,citem_id,cDefine12,cDefine13) "; strSql = strSql + "VALUES(" + drgroupby[j]["会计期间"].ToString(); strSql = strSql + ",'" + drgroupby[j]["凭证类别"].ToString(); strSql = strSql + "','" + drgroupby[j]["凭证类别"].ToString(); strSql = strSql + "','" + drgroupby[j]["凭证ID"].ToString(); strSql = strSql + "','" + drgroupby[j]["摘要"].ToString(); strSql = strSql + "','" + drgroupby[j]["原凭证号"].ToString(); strSql = strSql + "','" + "GL"; //这里外部系统设置为总账,否则导入的凭证默认无法修改。 strSql = strSql + "','" + userid; strSql = strSql + "'," + (j + 1).ToString(); //行号 strSql = strSql + ",'" + drgroupby[j]["科目编码"].ToString(); strSql = strSql + "','" + drgroupby[j]["币种名称"].ToString(); strSql = strSql + "','" + drgroupby[j]["制单日期"].ToString(); strSql = strSql + "'," + 1; //bvouchedit strSql = strSql + "," + 1; //bvouchaddordele strSql = strSql + "," + 1; //bvouchmoneyhold strSql = strSql + "," + 1; //bvalueedit,bcodeedit strSql = strSql + "," + 1; //bcodeedit //外币借贷 if (string.IsNullOrEmpty(drgroupby[j]["借方原币金额"].ToString())) { strSql = strSql + "," + 0; //md_f } else { strSql = strSql + "," + drgroupby[j]["借方原币金额"].ToString(); //md } if (string.IsNullOrEmpty(drgroupby[j]["贷方原币金额"].ToString())) { strSql = strSql + "," + 0; //mc_f } else { strSql = strSql + "," + drgroupby[j]["贷方原币金额"].ToString(); //mc } //本位币借贷 if (string.IsNullOrEmpty(drgroupby[j]["借方本位币金额"].ToString())) { strSql = strSql + "," + 0; //md } else { strSql = strSql + "," + drgroupby[j]["借方本位币金额"].ToString(); //md } if (string.IsNullOrEmpty(drgroupby[j]["贷方本位币金额"].ToString())) { strSql = strSql + "," + 0; //mc } else { strSql = strSql + "," + drgroupby[j]["贷方本位币金额"].ToString(); //mc } //汇率 if (string.IsNullOrEmpty(drgroupby[j]["汇率"].ToString())) { strSql = strSql + "," + 0; } else { strSql = strSql + "," + drgroupby[j]["汇率"].ToString(); } strSql = strSql + ",'" + drgroupby[j]["部门编码"].ToString(); //部门编码 strSql = strSql + "','" + drgroupby[j]["职员编码"].ToString(); //职员编码 strSql = strSql + "','" + drgroupby[j]["客户编码"].ToString(); //客户编码 strSql = strSql + "','" + drgroupby[j]["供应商编码"].ToString(); //供应商编码 strSql = strSql + "','" + drgroupby[j]["项目大类编码"].ToString(); //物料大类编码 strSql = strSql + "','" + drgroupby[j]["项目编码"].ToString(); //物料编码 strSql = strSql + "','" + drgroupby[j]["政府项目"].ToString(); //政府项目 strSql = strSql + "','" + drgroupby[j]["资金来源"].ToString() + "')"; //资金来源 rs = conn.Execute(strSql, out rsaffected, -1); } //凭证导入U8中制单 bool glsaveflag = glcvoucher.SaveVoucher(); //回写凭证号及错误信息,一旦SaveVoucher成功执行完毕,数据库连接系统API自动关闭,需要再次打开 if (glsaveflag) { v_importsuccessrows = v_importsuccessrows + 1; int importedvoucherid; strSql = "SELECT distinct ino_id FROM " + strTempTable + " WHERE coutno_id ='" + vougroupby + "'"; conn.Open(dbconn); rs = conn.Execute(strSql, out rsaffected, -1); if (Convert.ToInt16(rs.Fields[0].Value) > 0) { importedvoucherid = Convert.ToInt16(rs.Fields[0].Value); } else { importedvoucherid = -1; } for (int j = 0; j < drgroupby.Count(); j++) { drgroupby[j]["是否导入"] = "Y"; drgroupby[j]["错误信息"] = ""; drgroupby[j]["凭证号"] = importedvoucherid; drgroupby[j]["制单人"] = userid; } } else { v_importfailurerows = v_importfailurerows + 1; //回写凭证号及错误信息 for (int j = 0; j < drgroupby.Count(); j++) { drgroupby[j]["是否导入"] = "N"; drgroupby[j]["错误信息"] = glcvoucher.strErrMessage; } } //删除导入接口表数据并关闭数据库连接 strSql = "DELETE FROM " + strTempTable; rs = conn.Execute(strSql, out rsaffected, -1); //复制已导入数据到返回数据表中 for (int k = 0; k < drgroupby.Count(); k++) { dsreturnvouchers.Tables["GLVouchers"].ImportRow(drgroupby[k]); } } //执行PerformStep()函数 importdataprogressBar.PerformStep(); //string str = Math.Round((100 * (i + 1.0) / dtdistinct.Rows.Count), 2).ToString("#0.00 ") + "%"; //System.Drawing.Font font = new System.Drawing.Font("Times New Roman", (float)10, FontStyle.Regular); //PointF pt = new PointF(this.importdataprogressBar.Width / 2 - 17, this.importdataprogressBar.Height / 2 - 7); //g.DrawString(str, font, Brushes.Yellow, pt); } //返回数据导入是否成功标志 importsuccessrows = v_importsuccessrows; importfailurerows = v_importfailurerows; conn.Close(); errmsg = ""; if (v_importfailurerows != 0) { return(false); } else { return(true); } }
// // Refresh data // public static Boolean bRefreshData(String sDatabaseLocation, Boolean bDebug) { clsLog clsLog_ = new clsLog(msLogFile, msVersionData); clsError clsError_ = new clsError(); String sSql = null; ADODB.Connection conn = new ADODB.Connection(); object RecordsAffected = null; try { clsLog_.mLog(Constants.gcInfo, "Refreshing data ..."); if (bDebug) { clsLog_.mLog(Constants.gcInfo, sIndent + "onelan.bRefreshData, database =" + sDatabaseLocation +"\\" + gsOneLanDatabase); } // Open connection to database conn.Open(gcDSN + sDatabaseLocation + "\\" + gsOneLanDatabase); //1.1 Purge accom table for properities no longer current sSql = "DELETE FROM accom a" + " WHERE NOT EXISTS (" + " SELECT 1" + " FROM PropertyDetailsRC rc" + " WHERE rc.propid = a.propid" + " AND rc.sold =0 and pending = 0)"; conn.Execute(sSql, out RecordsAffected, -1); //1.2 Purge Property table for properities no longer current sSql = "DELETE FROM Property p" + " WHERE NOT EXISTS (" + " SELECT 1" + " FROM PropertyDetailsRC rc" + " WHERE rc.propid = p.propid" + " AND rc.sold =0 AND pending =0)"; conn.Execute(sSql, out RecordsAffected, -1); //1.3 Add new properties sSql = "INSERT INTO Property (" + " PropID" + ",PropertyAddress1" + ",PropertyAddress2" + ",PropertyAddress3" + ",PropertyAddress4" + ",PostCode" + ",Price" + ",OffersOverEtc" + ",RCCWOffice" + ",UnderOffer" + ",ClosingDate )"; sSql = sSql + "SELECT " + " rc.PropID" + ",rc.PropertyAddress1" + ",rc.PropertyAddress2" + ",rc.PropertyAddress3" + ",rc.PropertyAddress4" + ",rc.PostCode" + ",rc.Price" + ",rc.OffersOverEtc" + ",rc.RCCWOffice" + ",iif(ISNULL(rc.OfferDate),0,1)" + ",rc.ClosingDate" + " FROM PropertyDetailsRC rc" + " WHERE rc.sold=0" + " AND pending =0" + " AND NOT EXISTS (" + " SELECT 1" + " FROM Property p2" + " WHERE p2.propid = rc.propid)"; conn.Execute(sSql, out RecordsAffected, -1); // UNderOffer // clsLog_.mLog("INFO", sSql); // Debug.WriteLine(" insert sql = " + sSql); //1.4 Update property details to ensure current with main system sSql = "UPDATE property p," + " PropertyDetailsRC rc SET " + " p.PropertyAddress1 = rc.PropertyAddress1" + " ,p.PropertyAddress2 = rc.PropertyAddress2" + " ,p.PropertyAddress3 = rc.PropertyAddress3" + " ,p.PropertyAddress4 = rc.PropertyAddress4" + " ,p.PostCode= rc.PostCode" + " ,p.Price= rc.Price" + " ,p.OffersOverEtc= rc.OffersOverEtc" + " ,p.ClosingDate= rc.ClosingDate" + " ,p.UnderOffer = iif(ISNULL(rc.OfferDate),0,1)" + " WHERE p.Propid = rc.Propid" + " AND (" + " p.PropertyAddress1 <> rc.PropertyAddress1" + " OR p.PropertyAddress2 <> rc.PropertyAddress2" + " OR p.PropertyAddress3 <> rc.PropertyAddress3" + " OR p.PropertyAddress4 <> rc.PropertyAddress4" + " OR p.PostCode <> rc.PostCode" + " OR p.Price <> rc.Price" + " OR p.OffersOverEtc <> rc.OffersOverEtc" + " OR p.ClosingDate <> rc.ClosingDate" + " OR (p.UnderOffer= '0' AND rc.OfferDate IS NOT NULL)" + " )"; // clsLog_.mLog("INFO", sSql); conn.Execute(sSql, out RecordsAffected, -1); // Fix the closing date //1.4.1 Update null closing date to 00:00:00 sSql = "Update Property" + " Set closingdate = '00:00:00'" + " Where closingdate Is Null or format(closingDate,'dd-mmm-yyyy')=''"; conn.Execute(sSql, out RecordsAffected, -1); //1.4 Add new ACC sSql = " INSERT INTO ACCOM (Propid,Accom, Website)" + " SELECT rca.Propid,rca.Accom, rca.Website" + " FROM ACCOMRC rca," + " property p " + " WHERE p.propid= rca.propid" + " AND NOT EXISTS (" + " SELECT 1" + " FROM accom a" + " WHERE a.propid = p.propid)"; conn.Execute(sSql, out RecordsAffected, -1); conn.Close(); return true; } catch (Exception e) { clsError_.mLogError("Problem running onelan interface", "onelan", "bRefreshData", e, msVersionData, msLogFile,false); return false; } }
private void btnOK_Click(object sender, EventArgs e) { ADODB.Connection objCon; DialogResult update; string[,] argArray; object[,] idxUpdate; object[] rtnList; object[] exTest; int numTests; string tstList; string argID; string itmList; string msgString; string tstID; string strCon; string strSQL; objCon = new ADODB.Connection(); itmList = ""; tstList = ""; strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" + "database=functional_test_data;option=3"; //get the number of checkboxes that are checked from dbUpdater form idxUpdate = chkBoxNumChecked(chkArgument); for (int j = 0; j < idxUpdate.GetLength(0); j++) itmList = itmList + idxUpdate[j, 0] + "\t" + idxUpdate[j, 1] + "\r\n"; //dimension an array for the arguments will be going into the database argArray = new string[idxUpdate.GetLength(0), 2]; for (int z = 0; z < argArray.GetLength(0); z++) { argArray[z, 0] = idxUpdate[z, 0].ToString(); argArray[z, 1] = lblArgument[Convert.ToInt32(idxUpdate[z, 0])].Text; } //get the test ID strSQL = "SELECT id FROM test WHERE name = '" + tstname + "'"; rtnList = db_access(strSQL); tstID = rtnList[0].ToString(); //get the argument set id strSQL = "SELECT argument_set_id FROM step WHERE (test_id = '" + tstID + "' AND number = " + thsStep[0] + ")"; rtnList = db_access(strSQL); argID = rtnList[0].ToString(); strSQL = "SELECT COUNT(*) FROM step WHERE argument_set_id = '" + argID + "'"; rtnList = db_access(strSQL); numTests = Convert.ToInt32(rtnList[0]); if (numTests <= 1) { if (numTests != 0) { //get the argument set id strSQL = "SELECT test_id FROM step WHERE argument_set_id = '" + argID + "'"; rtnList = db_access(strSQL); } } else { strSQL = "SELECT test_id FROM step WHERE (argument_set_id = '" + argID + "' AND test_id <> (SELECT id FROM test WHERE name = '" + tstname + "'))"; rtnList = db_access(strSQL); } if (rtnList.Length != 0) { for (int k = 0; k < rtnList.Length; k++) { strSQL = "SELECT name from test WHERE id = '" + rtnList[k] + "'"; exTest = db_access(strSQL); tstList = tstList + exTest[0] + "\r\n"; } msgString = "You will be updating Step " + thsStep[0] + " of the " + tstname + " test.\r\nThe following data items will be updated\r\n\r\nIndex#\tData Item\r\n" + itmList + "\r\nYou will not be able to undo these changes once they are done. " + "The argument set that you are updating is also used in the following tests:\r\n\r\n" + tstList + "\r\nAre you sure you want to Continue?\r\n\r\nPress 'Yes' to Update. Press 'No' to exit"; } else { msgString = "You will be updating Step " + thsStep[0] + " of the " + tstname + " test.\r\nThe following data items will be updated\r\n\r\nIndex#\tData Item\r\n" + itmList + "\r\nYou will not be able to undo these changes once they are done. " + "The argument set that you are updating is not used in any other tests>" + "\r\nAre you sure you want to Continue?\r\n\r\nPress 'Yes' to Update. Press 'No' to exit"; } update = MessageBox.Show(msgString, "Database Updater", MessageBoxButtons.YesNo, MessageBoxIcon.Information); if (update == DialogResult.Yes) { objCon.Open(strCon); for (int x = 0; x < argArray.GetLength(0); x++) { strSQL = "UPDATE argument SET value='" + argArray[x, 1] + "' WHERE argument_set_id = '" + argID + "' AND seq = " + (Convert.ToInt32(argArray[x, 0]) + 1).ToString(); objCon.Execute(strSQL, out missing, 0); } objCon.Close(); MessageBox.Show("Database rows updated.\r\nDone! ", "Database Updater", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } }