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();
}
}
