public TransactionInMaster GetTransactionInMaster(string transactionID)
{
TransactionInMaster obj = null;
string commandText = OleDBHelper.GetQueryText("TransactionInMaster_SelectID", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@TransactionID", transactionID)
};
using (DataTable table = OleDBHelper.ExecuteParamerizedSelectCommand(commandText, CommandType.Text, parameters))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
obj = new TransactionInMaster();
obj.TransactionID = Convert.ToString(table.Rows[0]["TransactionID"]);
obj.TransactionDate = Convert.ToDateTime(table.Rows[0]["TransactionDate"]);
obj.PartyID = Convert.ToString(table.Rows[0]["PartyID"]);
obj.Amount = Convert.ToDecimal(Convert.IsDBNull(table.Rows[0]["Amount"]) ? 0 : table.Rows[0]["Amount"]);
obj.Remarks = Convert.ToString(table.Rows[0]["Remarks"]);
}
}
return(obj);
}
public PartyMaster GetPartyMasterDetails(String partyID)
{
PartyMaster obj = null;
string commandText = OleDBHelper.GetQueryText("PartyMaster_SelectID", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@PartyID", partyID)
};
using (DataTable table = OleDBHelper.ExecuteParamerizedSelectCommand(commandText, CommandType.Text, parameters))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
obj = new PartyMaster();
obj.PartyID = Convert.ToString(table.Rows[0]["PartyID"]);
obj.PartyName = Convert.ToString(table.Rows[0]["PartyName"]);
obj.PersonName = Convert.ToString(table.Rows[0]["PersonName"]);
obj.Address = Convert.ToString(table.Rows[0]["Address"]);
obj.ContactNo = Convert.ToString(table.Rows[0]["ContactNo"]);
obj.Mobile = Convert.ToString(table.Rows[0]["Mobile"]);
obj.EamilAddress = Convert.ToString(table.Rows[0]["EamilAddress"]);
obj.Remarks = Convert.ToString(table.Rows[0]["Remarks"]);
}
}
return(obj);
}
public ItemMaster GetItemMasterDetails(String itemID)
{
ItemMaster obj = null;
string commandText = OleDBHelper.GetQueryText("ItemMaster_SelectID", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@ItemID", itemID)
};
using (DataTable table = OleDBHelper.ExecuteParamerizedSelectCommand(commandText, CommandType.Text, parameters))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
obj = new ItemMaster();
obj.ItemID = Convert.ToString(table.Rows[0]["ItemID"]);
obj.ItemName = Convert.ToString(table.Rows[0]["ItemName"]);
obj.Remarks = Convert.ToString(table.Rows[0]["Remarks"]);
}
}
return(obj);
}
public List <TransactionInMaster> GetTransactionInMasterList()
{
List <TransactionInMaster> list = null;
string commandText = OleDBHelper.GetQueryText("TransactionInMaster_SelectAll", QueryType.Views);
using (DataTable table = OleDBHelper.ExecuteSelectCommand(commandText, CommandType.Text))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
list = new List <TransactionInMaster>();
//Now lets populate the employee details into the list of employees
foreach (DataRow row in table.Rows)
{
TransactionInMaster obj = new TransactionInMaster();
obj.TransactionID = Convert.ToString(row["TransactionID"]);
obj.TransactionDate = Convert.ToDateTime(row["TransactionDate"]);
obj.PartyID = Convert.ToString(row["PartyID"]);
obj.Amount = Convert.ToDecimal(Convert.IsDBNull(row["Amount"]) ? 0 : row["Amount"]);
obj.Remarks = Convert.ToString(row["Remarks"]);
list.Add(obj);
}
}
}
return(list);
}
public Inventory GetInventoryDetails(Inventory obj)
{
Inventory objDetails = null;
string commandText = OleDBHelper.GetQueryText("Inventory_SelectID", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@ItemID", obj.ItemID)
//new OleDbParameter("@UnitID", obj.UnitID)
};
using (DataTable table = OleDBHelper.ExecuteParamerizedSelectCommand(commandText, CommandType.Text, parameters))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
objDetails = new Inventory();
objDetails.ItemID = Convert.ToString(table.Rows[0]["ItemID"]);
objDetails.UnitID = Convert.ToString(table.Rows[0]["UnitID"]);
objDetails.LastTranID = Convert.ToString(table.Rows[0]["LastTranID"]);
objDetails.QtyIn = Convert.IsDBNull(table.Rows[0]["QtyIn"])? 0 : Convert.ToInt32(table.Rows[0]["QtyIn"]);
objDetails.QtyOut = Convert.IsDBNull(table.Rows[0]["QtyOut"]) ? 0 : Convert.ToInt32(table.Rows[0]["QtyOut"]);
objDetails.PrevBalance = Convert.IsDBNull(table.Rows[0]["PrevBalance"]) ? 0 : Convert.ToInt32(table.Rows[0]["PrevBalance"]);
objDetails.CurrBalance = Convert.IsDBNull(table.Rows[0]["CurrBalance"]) ? 0 : Convert.ToInt32(table.Rows[0]["CurrBalance"]);
}
}
return(objDetails);
}
public bool AddNewInventory(List <Inventory> objList)
{
string commandText = OleDBHelper.GetQueryText("Inventory_Insert", QueryType.Procedures);
int count = objList.Count;
OleDbParameter[] parameters;
bool result = false;
if (count > 0)
{
foreach (Inventory obj in objList)
{
parameters = new OleDbParameter[]
{
new OleDbParameter("@ItemID", obj.ItemID),
new OleDbParameter("@UnitID", obj.UnitID),
new OleDbParameter("@LastTranID", obj.LastTranID),
new OleDbParameter("@QtyIn", obj.QtyIn),
new OleDbParameter("@QtyOut", obj.QtyOut),
new OleDbParameter("@PrevBalance", obj.PrevBalance),
new OleDbParameter("@CurrBalance", obj.CurrBalance),
new OleDbParameter("@CreatedOn", GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now))
};
result = OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters);
if (result == false)
{
break;
}
}
}
return(result);
}
public List <ItemMaster> GetItemMasterList()
{
List <ItemMaster> list = null;
string commandText = OleDBHelper.GetQueryText("ItemMaster_SelectAll", QueryType.Views);
using (DataTable table = OleDBHelper.ExecuteSelectCommand(commandText, CommandType.Text))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
list = new List <ItemMaster>();
//Now lets populate the employee details into the list of employees
foreach (DataRow row in table.Rows)
{
ItemMaster obj = new ItemMaster();
obj.ItemID = Convert.ToString(row["ItemID"]);
obj.ItemName = Convert.ToString(row["ItemName"]);
obj.Remarks = Convert.ToString(row["Remarks"]);
list.Add(obj);
}
}
}
return(list);
}
public BooksMaster GetBooksMasterDetails(String bookID)
{
BooksMaster obj = null;
string commandText = OleDBHelper.GetQueryText("BooksMaster_SelectID", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@BookID", bookID)
};
using (DataTable table = OleDBHelper.ExecuteParamerizedSelectCommand(commandText, CommandType.Text, parameters))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
obj = new BooksMaster();
obj.BookID = Convert.ToString(table.Rows[0]["BookID"]);
obj.ISBN = Convert.ToString(table.Rows[0]["ISBN"]);
obj.Title = Convert.ToString(table.Rows[0]["Title"]);
obj.Subject = Convert.ToString(table.Rows[0]["Subject"]);
obj.Author = Convert.ToString(table.Rows[0]["Author"]);
obj.Publication = Convert.ToString(table.Rows[0]["Publication"]);
obj.EditionNo = Convert.ToInt32(table.Rows[0]["EditionNo"]);
obj.EditionYear = Convert.ToInt32(table.Rows[0]["EditionYear"]);
obj.Price = Convert.ToInt32(table.Rows[0]["Price"]);
}
}
return(obj);
}
public bool AddNewTransactionInDetails(List <BooksTransactionInDetails> objList)
{
string commandText = OleDBHelper.GetQueryText("BooksTransactionInDetails_Insert", QueryType.Procedures);
int count = objList.Count;
OleDbParameter[] parameters;
bool result = false;
if (count > 0)
{
foreach (BooksTransactionInDetails obj in objList)
{
parameters = new OleDbParameter[]
{
new OleDbParameter("@TransactionID", obj.TransactionID),
new OleDbParameter("@BookID", obj.BookMaster.BookID),
new OleDbParameter("@Unit", obj.Unit)
};
result = OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters);
if (result == false)
{
break;
}
}
}
return(result);
}
public string GetAccessTable()
{
string strConn = string.Format("provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0}" + @"\ThinkCRMCo4Developer.mdb");
OleDBHelper olr = new OleDBHelper("");
return("");
// olr.ExecuteTable()
}
public bool AddNewItemMaster(ItemMaster obj)
{
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@ItemID", obj.ItemID),
new OleDbParameter("@ItemName", obj.ItemName),
new OleDbParameter("@Remarks", obj.Remarks),
new OleDbParameter("@CreatedOn", GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now))
};
string commandText = OleDBHelper.GetQueryText("ItemMaster_Insert", QueryType.Procedures);
return(OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters));
}
public static bool UpdateUniqueCodeForTable(string tableName)
{
bool result;
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@TableName", tableName)
};
string commandText = OleDBHelper.GetQueryText("UniqueCodeMaster_Update", QueryType.Procedures);
result = OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters);
return(result);
}
public static DataTable GetUniqueCodeForTable(string tableName)
{
DataTable dt = new DataTable();
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@TableName", tableName)
};
string commandText = OleDBHelper.GetQueryText("GetUniqueCodeForTable", QueryType.Procedures);
dt = OleDBHelper.ExecuteParamerizedSelectCommand(commandText, CommandType.Text, parameters);
return(dt);
}
// Same can be used for deactive user
public bool DeleteTransactionOutDetails(string transactionID)
{
string commandText = OleDBHelper.GetQueryText("TransactionOutDetails_Delete", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@TransactionID", transactionID)
};
bool result = false;
result = OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters);
return(result);
}
public bool AddNewAccount(Account account)
{
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@ID", account.ID),
new OleDbParameter("@AccountHolderName", account.AccountHolderName),
new OleDbParameter("@DOB", account.DateOfBirth),
new OleDbParameter("@IsActive", 1),
new OleDbParameter("@CreatedOn", GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now))
};
string commandText = DBQuery.GetQueryText("AccountMaster1_Insert");
return(OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters));
}
public bool UpdateTransactionOutMaster(BooksTransactionOutMaster obj)
{
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@TransactionDate", obj.TransactionDate),
new OleDbParameter("@Remarks", obj.Remarks),
new OleDbParameter("@CreatedOn", GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now)),
new OleDbParameter("@TransactionID", obj.TransactionID)
};
string commandText = OleDBHelper.GetQueryText("BooksTransactionOutMaster_Update", QueryType.Procedures);
return(OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters));
}
public List <BooksMaster> SearchBooksMasterList(string searchText)
{
searchText = "%" + searchText + "%";
List <BooksMaster> list = null;
string commandText = OleDBHelper.GetQueryText("Search_BooksMaster", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@BookID", searchText),
new OleDbParameter("@ISBN", searchText),
new OleDbParameter("@Title]", searchText),
new OleDbParameter("@Subject", searchText),
new OleDbParameter("@Author", searchText),
new OleDbParameter("@Publication", searchText)
//new OleDbParameter("@EditionNo", searchText),
//new OleDbParameter("@EditionYear", searchText)
//new OleDbParameter("@Price", book.Price),
};
using (DataTable table = OleDBHelper.ExecuteParamerizedSelectCommand(commandText, CommandType.Text, parameters))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
list = new List <BooksMaster>();
//Now lets populate the employee details into the list of employees
foreach (DataRow row in table.Rows)
{
BooksMaster obj = new BooksMaster();
obj.BookID = Convert.ToString(row["BookID"]);
obj.ISBN = Convert.ToString(row["ISBN"]);
obj.Title = Convert.ToString(row["Title"]);
obj.Subject = Convert.ToString(row["Subject"]);
obj.Author = Convert.ToString(row["Author"]);
obj.Publication = Convert.ToString(row["Publication"]);
obj.EditionNo = Convert.ToInt32(row["EditionNo"]);
obj.EditionYear = Convert.ToInt32(row["EditionYear"]);
obj.Price = Convert.ToInt32(row["Price"]);
list.Add(obj);
}
}
}
return(list);
}
public bool AddNewTransactionInMaster(TransactionInMaster obj)
{
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@TransactionID", obj.TransactionID),
new OleDbParameter("@TransactionDate", obj.TransactionDate),
new OleDbParameter("@PartyID", obj.PartyID),
new OleDbParameter("@Amount", obj.Amount),
new OleDbParameter("@Remarks", obj.Remarks),
new OleDbParameter("@CreatedOn", GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now))
};
string commandText = OleDBHelper.GetQueryText("TransactionInMaster_Insert", QueryType.Procedures);
return(OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters));
}
public bool AddNewPartyMaster(PartyMaster obj)
{
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@PartyID", obj.PartyID),
new OleDbParameter("@PartyName", obj.PartyName),
new OleDbParameter("@PersonName", obj.PersonName),
new OleDbParameter("@Address", obj.Address),
new OleDbParameter("@ContactNo", obj.ContactNo),
new OleDbParameter("@Mobile", obj.Mobile),
new OleDbParameter("@EamilAddress", obj.EamilAddress),
new OleDbParameter("@Remarks", obj.Remarks),
new OleDbParameter("@CreatedOn", GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now))
};
string commandText = OleDBHelper.GetQueryText("PartyMaster_Insert", QueryType.Procedures);
return(OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters));
}
public List <TransactionInDetails> GetTransactionInList(string transactionID)
{
List <TransactionInDetails> list = null;
string commandText = OleDBHelper.GetQueryText("TransactionInDetails_SelectID", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@TransactionID", transactionID)
};
using (DataTable table = OleDBHelper.ExecuteParamerizedSelectCommand(commandText, CommandType.Text, parameters))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
list = new List <TransactionInDetails>();
ItemMaster itemHandler = new ItemMaster();
//Now lets populate the employee details into the list of employees
foreach (DataRow row in table.Rows)
{
TransactionInDetails obj = new TransactionInDetails();
obj.TransactionID = Convert.ToString(row["TransactionID"]);
obj.ItemMaster = itemHandler.GetItemMasterDetails(Convert.ToString(row["ItemID"]));
obj.Unit = Convert.ToInt32(row["Unit"]);
obj.Rate = Convert.ToDecimal(Convert.IsDBNull(row["Rate"]) ? 0 : row["Rate"]);
obj.Amount = Convert.ToDecimal(Convert.IsDBNull(row["Amount"]) ? 0 : row["Amount"]);
//obj.Subject = Convert.ToString(row["Subject"]);
//obj.Author = Convert.ToString(row["Author"]);
//obj.Publication = Convert.ToString(row["Publication"]);
//obj.EditionNo = Convert.ToInt32(row["EditionNo"]);
//obj.EditionYear = Convert.ToInt32(row["EditionYear"]);
//obj.Price = Convert.ToInt32(row["Price"]);
list.Add(obj);
}
}
}
return(list);
}
public List <Inventory> GetInventoryList()
{
List <Inventory> list = null;
string commandText = OleDBHelper.GetQueryText("Inventory_SelectAll", QueryType.Views);
OleDbParameter[] parameters = new OleDbParameter[]
{
//new OleDbParameter("@TransactionID", transactionID)
};
using (DataTable table = OleDBHelper.ExecuteSelectCommand(commandText, CommandType.Text))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
list = new List <Inventory>();
Inventory obj;
ItemMaster objItemMaster;
//Now lets populate the employee details into the list of employees
foreach (DataRow row in table.Rows)
{
obj = new Inventory();
objItemMaster = new ItemMaster();
objItemMaster.ItemID = Convert.ToString(row["ItemID"]);
objItemMaster.ItemName = Convert.ToString(row["ItemName"]);
//obj.LastTranID = Convert.ToString(row["LastTranID"]);
obj.ItemMaster = objItemMaster;
obj.QtyIn = Convert.IsDBNull(row["QtyIn"]) ? 0 : Convert.ToInt32(row["QtyIn"]);
obj.QtyOut = Convert.IsDBNull(row["QtyOut"]) ? 0 : Convert.ToInt32(row["QtyOut"]);
//obj.CurrBalance = Convert.IsDBNull(row["QtyBalance"]) ? 0 : Convert.ToInt32(row["QtyBalance"]);
list.Add(obj);
}
}
}
return(list);
}
public bool AddNewBooksMaster(BooksMaster book)
{
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@BookID", book.BookID),
new OleDbParameter("@ISBN", book.ISBN),
new OleDbParameter("@Title", book.Title),
new OleDbParameter("@Title", book.Subject),
new OleDbParameter("@Author", book.Author),
new OleDbParameter("@Publication", book.Publication),
new OleDbParameter("@EditionNo", book.EditionNo),
new OleDbParameter("@EditionYear", book.EditionYear),
new OleDbParameter("@Price", book.Price),
new OleDbParameter("@CreatedOn", GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now))
};
string commandText = OleDBHelper.GetQueryText("BooksMaster_Insert", QueryType.Procedures);
return(OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters));
}
public bool UpdateInventory(Inventory obj)
{
string commandText = OleDBHelper.GetQueryText("Inventory_Update", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@LastTranID", obj.LastTranID),
new OleDbParameter("@QtyIn", obj.QtyIn),
new OleDbParameter("@QtyOut", obj.QtyOut),
new OleDbParameter("@PrevBalance", obj.PrevBalance),
new OleDbParameter("@CurrBalance", obj.CurrBalance),
new OleDbParameter("@UpdatedOn", GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now)),
new OleDbParameter("@ItemID", obj.ItemID)
//new OleDbParameter("@UnitID", obj.UnitID)
};
return(OleDBHelper.ExecuteNonQuery(commandText, CommandType.Text, parameters));
}
public DataTable GetBooksMasterTable()
{
DataTable dt = null;
string commandText = OleDBHelper.GetQueryText("rptBooksMaster", QueryType.Views);
using (DataTable table = OleDBHelper.ExecuteSelectCommand(commandText, CommandType.Text))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
dt = new DataTable();
dt = table;
}
}
return(dt);
}
public List <BooksMaster> GetBooksMasterList()
{
List <BooksMaster> list = null;
string commandText = OleDBHelper.GetQueryText("BooksMaster_SelectAll", QueryType.Views);
using (DataTable table = OleDBHelper.ExecuteSelectCommand(commandText, CommandType.Text))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
list = new List <BooksMaster>();
//Now lets populate the employee details into the list of employees
foreach (DataRow row in table.Rows)
{
BooksMaster obj = new BooksMaster();
obj.BookID = Convert.ToString(row["BookID"]);
obj.ISBN = Convert.ToString(row["ISBN"]);
obj.Title = Convert.ToString(row["Title"]);
obj.Subject = Convert.ToString(row["Subject"]);
obj.Author = Convert.ToString(row["Author"]);
obj.Publication = Convert.ToString(row["Publication"]);
obj.EditionNo = Convert.ToInt32(row["EditionNo"]);
obj.EditionYear = Convert.ToInt32(row["EditionYear"]);
obj.Price = Convert.ToInt32(row["Price"]);
list.Add(obj);
}
}
}
return(list);
}
public List <PartyMaster> GetPartyMasterList()
{
List <PartyMaster> list = null;
string commandText = OleDBHelper.GetQueryText("PartyMaster_SelectAll", QueryType.Views);
using (DataTable table = OleDBHelper.ExecuteSelectCommand(commandText, CommandType.Text))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
list = new List <PartyMaster>();
//Now lets populate the employee details into the list of employees
foreach (DataRow row in table.Rows)
{
PartyMaster obj = new PartyMaster();
obj.PartyID = Convert.ToString(row["PartyID"]);
obj.PartyName = Convert.ToString(row["PartyName"]);
obj.PersonName = Convert.ToString(row["PersonName"]);
obj.Address = Convert.ToString(row["Address"]);
obj.ContactNo = Convert.ToString(row["ContactNo"]);
obj.Mobile = Convert.ToString(row["Mobile"]);
obj.EamilAddress = Convert.ToString(row["EamilAddress"]);
obj.Remarks = Convert.ToString(row["Remarks"]);
list.Add(obj);
}
}
}
return(list);
}
//DataTable
public DataTable GetReportTransactionIn(string transactionID)
{
DataTable dt = null;
string commandText = OleDBHelper.GetQueryText("rptTransactionIn", QueryType.Procedures);
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@TransactionID", transactionID)
};
using (DataTable table = OleDBHelper.ExecuteParamerizedSelectCommand(commandText, CommandType.Text, parameters))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
dt = new DataTable();
dt = table;
}
}
return(dt);
}
public void ParseResult(string strSource, ref string strResult, ref string strReserved, ref string strCmd)
{
#region 设置为串口通讯的时候
//char c03 = Convert.ToChar(0x03);
//char c0f = Convert.ToChar(0x0F);
//char c00 = Convert.ToChar(0x00);
//#region
////存储每次接收后的10进制字符串
//char[] chars = strSource.ToCharArray();
//writelog.Write(strDevice, "接收数据开始:"+strSource, "log");
//string output = "";
//for (int i = 0; i < chars.Length; i++)
//{
// output = output + Convert.ToInt32(chars[i]) + " ";
//}
//writelog.Write(strDevice, "10进制接收数据:"+output, "log");
////
//#endregion
////去掉空
//strSource = strSource.Replace(c00, ' ').Trim();
//strResult += strSource;
//if (strSource.EndsWith(c03+""))
//{
// try
// {
// bool isSampleBar = true;
// //将接收到的字符串写入到日志文件中
// writelog.Write(strDevice, "接收数据开始：\r\n " + strResult, "log");
// writelog.Write(strDevice, "接收数据完成 ", "log");
// StreamReader sr = new StreamReader(@".\MVIS2010.txt");
// string correspondence = sr.ReadLine();
// sr.Close();
// string[] correspondenceArray = correspondence.Split('|');
// StringBuilder sBuilder = new StringBuilder("");
// string[] ss = strResult.Split(new char[] { '\r','\n' }, StringSplitOptions.RemoveEmptyEntries);
// string ChannelCode = "";
// for (int i = 0; i < ss.Length; i++)
// {
// if (ss[i].Trim().ToUpper().StartsWith("SN"))
// {
// strSampleNo = ss[i].Split(':')[1].Trim();
// }
// if (ss[i].Trim().ToUpper().StartsWith("DATE"))
// {
// strTestTime = ss[i].Replace("Date:","").Trim();
// }
// for (int j = 0; j < correspondenceArray.Length; j++)
// {
// string sTemp = ss[i].Trim();
// string lis = correspondenceArray[j].Split(',')[0];
// string zlchs = correspondenceArray[j].Split(',')[1];
// if (ss[i].StartsWith(lis))
// {
// string itemValue = ss[i].Split(':')[1].Trim();
// if (itemValue.IndexOf(" ") > -1)
// itemValue = itemValue.Split(' ')[0];
// sBuilder.Append(zlchs + ',' + itemValue + '|');
// }
// }
// }
// string str = sBuilder.ToString().Remove(sBuilder.Length - 1, 1);
// writelog.Write(strDevice, "数据处理结果:" + str, "log");
// string[] strs = str.Split('|');
// string ChannelType = ""; //0-普通结果;1-直方图;2-散点图;3-直方图界标;4-散点图界标;5-BASE64
// string testItemID = "";
// string TestResultValue = "";
// for (int i = 0; i < strs.Length; i++)
// {
// FindRow = tItemChannel.Select("通道编码='" + strs[i].Split(',')[0] + "'");
// if (FindRow.Length == 0) //无普通结果则查找图像能道，无图像通道则更新通道类型为空
// {
// ChannelType = null;
// writelog.Write(strDevice, "未设置通道：" + strs[i].Split(',')[0], "log");
// }
// else
// {
// testItemID = FindRow[0]["项目id"].ToString();
// ChannelType = "0"; //普通结果
// TestResultValue = TestResultValue + testItemID + "^" + strs[i].Split(',')[1] + "|";
// }
// }
// TestResultValue = strTestTime + "|" + strSampleNo + "^" + strSampleType + "^" + strBarCode + "|" + strOperator + "|" + StrSpecimen + "|" + "|" + TestResultValue;
// saveResult = new SaveResult();
// if (!string.IsNullOrEmpty(strSampleNo))
// {
// saveResult.SaveTextResult(strInstrument_id, TestResultValue, TestGraph, DrSampleNoField); if (ImmediatelyUpdate)
// {
// saveResult.UpdateData();
// }
// }
// if (!ImmediatelyUpdate)
// {
// saveResult.UpdateData();
// }
// }
// catch (Exception e)
// {
// writelog.Write(strDevice, "处理失败： " + e.ToString(), "log");
// }
// strResult = "";
//}
//strCmd = Convert.ToChar(0x06) + "";
#endregion
try
{
string file = "MVIS2010样本号重复处理.txt";
StreamReader sr = new StreamReader(@".\MVIS2010.txt", System.Text.Encoding.GetEncoding("GB2312"));
string correspondence = sr.ReadLine();
string[] correspondenceArray = correspondence.Split('|');
string configPath = sr.ReadLine();
string configPathSrc = configPath;
string password = sr.ReadLine();
string dateTime = DateTime.Now.ToString("yyyy-MM-dd");
sr.Close();
//拆分路径，寻找地址
string[] configPaths = configPath.Split('_');
string configPathRoot = configPaths[0] + '_' + configPaths[1] + '_';
string configPathEndStr = DateTime.Now.ToString("yyyy_MM");
configPath = configPathRoot + configPathEndStr + ".mdb";
if (!File.Exists(configPath))
{
writelog.Write(strDevice,
configPath + "没有找到本月源数据,尝试寻找上一月", "log");
string configPathLastMonth = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01")).AddMonths(-1).ToString("yyyy_MM");
configPath = configPathRoot + configPathLastMonth + ".mdb";
if (!File.Exists(configPath))
{
writelog.Write(strDevice,
configPath + "没有找到上月源数据,尝试寻找原始配置文件", "log");
configPath = configPathSrc;;
if (!File.Exists(configPath))
{
writelog.Write(strDevice,
configPath + "没有找到,系统进入下一个循环周期", "log");
return;
}
}
}
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + configPath + ";Persist Security Info=False;Jet OLEDB:Database Password="******"select distinct(SN) as ItemNo from Test where Format(todaydate, 'yyyy-mm-dd')='" + dateTime + "'";
DataTable dt = OleDBHelper.GetOledbDataTable(sql, connStr);
strTestTime = dateTime;
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
StringBuilder sBuilder = new StringBuilder("");
strSampleNo = item["ItemNo"].ToString();
string sqlSelect = @"select * from Test where SN=in_编号 and Format(todaydate, 'yyyy-mm-dd')= 'in_clrq' ";
sqlSelect = sqlSelect.Replace("in_编号", strSampleNo);
sqlSelect = sqlSelect.Replace("in_clrq", dateTime);
DataTable dt2 = OleDBHelper.GetOledbDataTable(sqlSelect, connStr);
List <string> TestValues = new List <string>();
if (dt2.Rows.Count > 0)
{
foreach (DataRow item2 in dt2.Rows)
{
foreach (var subItem in correspondenceArray)
{
string lis = subItem.Split(',')[0].Trim();
string zlchs = subItem.Split(',')[1].Trim();
string itemName = zlchs;
double itemValue = Convert.ToDouble(item2[lis].ToString());
itemValue = Math.Round(itemValue, 2);
TestValues.Add(itemName + ":" + itemValue.ToString());
sBuilder.Append(itemName + ',' + itemValue + '|');
}
}
}
/////
if (Helper.CompareSampleNoAndTime(file, strSampleNo, strTestTime, TestValues))
{
continue;
}
string str = sBuilder.ToString().Remove(sBuilder.Length - 1, 1);
string[] strs = str.Split('|');
string ChannelType = ""; //0-普通结果;1-直方图;2-散点图;3-直方图界标;4-散点图界标;5-BASE64
string testItemID = "";
string TestResultValue = "";
for (int i = 0; i < strs.Length; i++)
{
FindRow = tItemChannel.Select("通道编码='" + strs[i].Split(',')[0] + "'");
if (FindRow.Length == 0) //无普通结果则查找图像能道，无图像通道则更新通道类型为空
{
ChannelType = null;
writelog.Write(strDevice, "未设置通道：" + strs[i].Split(',')[0], "log");
}
else
{
testItemID = FindRow[0]["项目id"].ToString();
ChannelType = "0"; //普通结果
TestResultValue = TestResultValue + testItemID + "^" + strs[i].Split(',')[1] + "|";
}
}
TestResultValue = strTestTime + "|" + strSampleNo + "^" + strSampleType + "^" + strBarCode + "|" + strOperator + "|" + StrSpecimen + "|" + "|" + TestResultValue;
saveResult = new SaveResult();
if (!string.IsNullOrEmpty(strSampleNo) || !string.IsNullOrEmpty(strBarCode))
{
saveResult.SaveTextResult(strInstrument_id, TestResultValue, TestGraph, DrSampleNoField);
if (ImmediatelyUpdate)
{
saveResult.UpdateData();
}
}
}
}
}
catch (Exception e)
{
writelog.Write(strDevice, "处理失败： " + e.ToString(), "log");
}
System.Threading.Thread.Sleep(3000);
}
public void ParseResult(string strSource, ref string strResult, ref string strReserved, ref string strCmd)
{
try
{
string file = "D280样本号重复处理.txt";
StreamReader sr = new StreamReader(@".\D280.txt", Encoding.Default);
string correspondence = sr.ReadLine();
string[] correspondenceArray = correspondence.Split('|');
string configPath = sr.ReadLine();
strTestTime = DateTime.Now.ToString("yyyy-MM-dd");
sr.Close();
if (!File.Exists(configPath))
{
writelog.Write(strDevice, "找不到数据库文件： " + configPath, "log");
return;
}
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + configPath + ";Persist Security Info=False;Jet OLEDB:Database Password=iamysh";
string sql = "select distinct(sample_id) as ItemNo from SAMPLE_ITEM_TEST_RESULT where format(time,'yyyy-MM-dd')='in_dateTimeName'";
sql = sql.Replace("in_dateTimeName", strTestTime);
DataTable dt = OleDBHelper.GetOledbDataTable(sql, connStr);
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
StringBuilder sBuilder = new StringBuilder("");
strSampleNo = item["ItemNo"].ToString();
string sqlSelect = @" select * from SAMPLE_ITEM_TEST_RESULT where sample_id='in_strSampleNo' and format(time,'yyyy-MM-dd')='in_dateTimeName' ";
sqlSelect = sqlSelect.Replace("in_dateTimeName", strTestTime);
sqlSelect = sqlSelect.Replace("in_strSampleNo", strSampleNo);
DataTable dt2 = OleDBHelper.GetOledbDataTable(sqlSelect, connStr);
List <string> TestValues = new List <string>();
if (dt2.Rows.Count > 0)
{
foreach (DataRow item2 in dt2.Rows)
{
string itemName = item2["item"].ToString().ToUpper();
string itemValue = item2["result"].ToString().ToUpper();
TestValues.Add(itemName + ":" + itemValue.ToString());
sBuilder.Append(itemName + ',' + itemValue + '|');
}
}
/////
if (Helper.CompareSampleNoAndTime(file, strSampleNo, strTestTime, TestValues))
{
continue;
}
string str = sBuilder.ToString().Remove(sBuilder.Length - 1, 1);
string[] strs = str.Split('|');
string ChannelType = ""; //0-普通结果;1-直方图;2-散点图;3-直方图界标;4-散点图界标;5-BASE64
string testItemID = "";
string TestResultValue = "";
for (int i = 0; i < strs.Length; i++)
{
FindRow = tItemChannel.Select("通道编码='" + strs[i].Split(',')[0] + "'");
if (FindRow.Length == 0) //无普通结果则查找图像能道，无图像通道则更新通道类型为空
{
ChannelType = null;
writelog.Write(strDevice, "未设置通道：" + strs[i].Split(',')[0], "log");
}
else
{
testItemID = FindRow[0]["项目id"].ToString();
ChannelType = "0"; //普通结果
TestResultValue = TestResultValue + testItemID + "^" + strs[i].Split(',')[1] + "|";
}
}
TestResultValue = strTestTime + "|" + strSampleNo + "^" + strSampleType + "^" + strBarCode + "|" + strOperator + "|" + StrSpecimen + "|" + "|" + TestResultValue;
saveResult = new SaveResult();
if (!string.IsNullOrEmpty(strSampleNo) || !string.IsNullOrEmpty(strBarCode))
{
saveResult.SaveTextResult(strInstrument_id, TestResultValue, TestGraph, DrSampleNoField);
if (ImmediatelyUpdate)
{
saveResult.UpdateData();
}
}
}
}
}
catch (Exception e)
{
writelog.Write(strDevice, "处理失败： " + e.ToString(), "log");
}
System.Threading.Thread.Sleep(5000);
}
public void ParseResult(string strSource, ref string strResult, ref string strReserved, ref string strCmd)
{
try
{
string file = "HZK320P样本号重复处理.txt";
StreamReader sr = new StreamReader(@".\HZK320P.txt", System.Text.Encoding.GetEncoding("GB2312"));
string correspondence = sr.ReadLine();
string[] correspondenceArray = correspondence.Split('|');
string configPath = sr.ReadLine();
string password = sr.ReadLine();
string dateTime = DateTime.Now.ToString("yyyy-MM-dd");
sr.Close();
//拆分路径，寻找地址
string configPathDateTime = DateTime.Now.ToString("yyyyMMdd");
configPath = configPath + "yb" + configPathDateTime + ".mdb";
if (!File.Exists(configPath))
{
writelog.Write(strDevice,
configPath + "没有找到,系统进入下一个循环周期", "log");
return;
}
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + configPath + ";Persist Security Info=False;Jet OLEDB:Database Password="******"select distinct(ybno) as ItemNo from zzjgb ";
DataTable dt = OleDBHelper.GetOledbDataTable(sql, connStr);
strTestTime = dateTime;
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
StringBuilder sBuilder = new StringBuilder("");
strSampleNo = item["ItemNo"].ToString();
string sqlSelect = @"select * from zzjgb where ybno=in_编号 ";
sqlSelect = sqlSelect.Replace("in_编号", strSampleNo);
DataTable dt2 = OleDBHelper.GetOledbDataTable(sqlSelect, connStr);
List <string> TestValues = new List <string>();
if (dt2.Rows.Count > 0)
{
foreach (DataRow item2 in dt2.Rows)
{
string itemName = item2["ywjc"].ToString();
double itemValue = Convert.ToDouble(item2["zzjg"].ToString());
itemValue = Math.Round(itemValue, 2);
TestValues.Add(itemName + ":" + itemValue.ToString());
sBuilder.Append(itemName + ',' + itemValue + '|');
}
}
/////
if (Helper.CompareSampleNoAndTime(file, strSampleNo, strTestTime, TestValues))
{
continue;
}
string str = sBuilder.ToString().Remove(sBuilder.Length - 1, 1);
string[] strs = str.Split('|');
string ChannelType = ""; //0-普通结果;1-直方图;2-散点图;3-直方图界标;4-散点图界标;5-BASE64
string testItemID = "";
string TestResultValue = "";
for (int i = 0; i < strs.Length; i++)
{
FindRow = tItemChannel.Select("通道编码='" + strs[i].Split(',')[0] + "'");
if (FindRow.Length == 0) //无普通结果则查找图像能道，无图像通道则更新通道类型为空
{
ChannelType = null;
writelog.Write(strDevice, "未设置通道：" + strs[i].Split(',')[0], "log");
}
else
{
testItemID = FindRow[0]["项目id"].ToString();
ChannelType = "0"; //普通结果
TestResultValue = TestResultValue + testItemID + "^" + strs[i].Split(',')[1] + "|";
}
}
TestResultValue = strTestTime + "|" + strSampleNo + "^" + strSampleType + "^" + strBarCode + "|" + strOperator + "|" + StrSpecimen + "|" + "|" + TestResultValue;
saveResult = new SaveResult();
if (!string.IsNullOrEmpty(strSampleNo) || !string.IsNullOrEmpty(strBarCode))
{
saveResult.SaveTextResult(strInstrument_id, TestResultValue, TestGraph, DrSampleNoField);
if (ImmediatelyUpdate)
{
saveResult.UpdateData();
}
}
}
}
}
catch (Exception e)
{
writelog.Write(strDevice, "处理失败： " + e.ToString(), "log");
}
System.Threading.Thread.Sleep(3000);
}
