public Boolean ApproveStockHoldingHeader(stockholdingheader shh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update StockHoldingHeader set DocumentStatus=99, status=1 " +
", ApproveUser='******'" +
", commentStatus='" + shh.CommentStatus + "'" +
", DocumentNo=" + shh.DocumentNo +
", DocumentDate=convert(date, getdate())" +
" where DocumentID='" + shh.DocumentID + "'" +
" and TemporaryNo=" + shh.TemporaryNo +
" and TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "StockHoldingHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean reverseStockHoldingHeader(stockholdingheader shh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update StockHoldingHeader set DocumentStatus=" + shh.DocumentStatus +
// ",QCStatus=" + mrnh.QCStatus +
", forwardUser='******'" +
", commentStatus='" + shh.CommentStatus + "'" +
", ForwarderList='" + shh.ForwarderList + "'" +
" where DocumentID='" + shh.DocumentID + "'" +
" and TemporaryNo=" + shh.TemporaryNo +
" and TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "StockHoldingHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public static List <stockholdingdetail> getStockHoldingHeaderDetail(stockholdingheader shh)
{
stockholdingdetail shd;
List <stockholdingdetail> StockHoldingHeaderDetailList = new List <stockholdingdetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID,DocumentID,DocumentName,TemporaryNo,TemporaryDate,StockItemID,StockItemName,ModelNo,ModelName,Quantity, " +
"InwardDocumentID,InwardDocumentNo,InwardDocumentDate,StockReferenceNo " +
"from ViewStockHoldingDetail " +
" where DocumentID='" + shh.DocumentID + "'" +
" and TemporaryNo=" + shh.TemporaryNo +
" and TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'" +
" order by StockItemID";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
shd = new stockholdingdetail();
shd.RowID = reader.GetInt32(0);
shd.DocumentID = reader.GetString(1);
shd.DocumentName = reader.GetString(2);
shd.TemporaryNo = reader.GetInt32(3);
shd.TemporaryDate = reader.GetDateTime(4).Date;
shd.StockItemID = reader.GetString(5);
shd.StockItemName = reader.GetString(6);
shd.ModelNo = reader.IsDBNull(7)?"NA":reader.GetString(7);
shd.ModelName = reader.IsDBNull(8) ? "NA" : reader.GetString(8);
shd.Quantity = reader.GetDouble(9);
shd.InwardDocumentID = reader.GetString(10);
shd.InwardDocumentNo = reader.GetString(11);
shd.InwardDocumentDate = reader.GetDateTime(12);
shd.StockReferenceNo = reader.GetInt32(13);
StockHoldingHeaderDetailList.Add(shd);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Stock Header Details");
}
return(StockHoldingHeaderDetailList);
}
public Boolean validateStockHoldingHeader(stockholdingheader shh)
{
Boolean status = true;
try
{
if (shh.StoreLocationID.Trim().Length == 0 || shh.StoreLocationID == null)
{
return(false);
}
if (shh.Remarks.Trim().Length == 0 || shh.Remarks == null)
{
return(false);
}
}
catch (Exception ex)
{
}
return(status);
}
public Boolean InsertSHHeaderAndDetail(stockholdingheader shh, List <stockholdingdetail> StockHoldingHeaderDetails)
{
Boolean status = true;
string utString = "";
string updateSQL = "";
try
{
shh.TemporaryNo = DocumentNumberDB.getNumber(shh.DocumentID, 1);
if (shh.TemporaryNo <= 0)
{
MessageBox.Show("Error in Creating New Number");
return(false);
}
updateSQL = "update DocumentNumber set TempNo =" + shh.TemporaryNo +
" where FYID='" + Main.currentFY + "' and DocumentID='" + shh.DocumentID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "insert into StockHoldingHeader " +
"(DocumentID,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," +
"StoreLocationID,Remarks,Comments,CommentStatus,CreateUser," +
"CreateTime,ForwarderList,Status,DocumentStatus)" +
" values (" +
"'" + shh.DocumentID + "'," +
shh.TemporaryNo + "," +
"'" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
shh.DocumentNo + "," +
"'" + shh.DocumentDate.ToString("yyyy-MM-dd") + "'," +
"'" + shh.StoreLocationID + "'," +
"'" + shh.Remarks + "'," +
"'" + shh.Comments + "'," +
"'" + shh.CommentStatus + "'," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" + "," +
"'" + shh.ForwarderList + "'," +
+shh.Status + "," +
+shh.DocumentStatus + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "StockHoldingHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from StockHoldingDetail where DocumentID='" + shh.DocumentID + "'" +
" and TemporaryNo=" + shh.TemporaryNo +
" and TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "StockHoldingDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (stockholdingdetail shd in StockHoldingHeaderDetails)
{
updateSQL = "insert into StockHoldingDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,Quantity,InwardDocumentID,InwardDocumentNo,InwardDocumentdate,StockReferenceNo) " +
"values ('" + shd.DocumentID + "'," +
shh.TemporaryNo + "," +
"'" + shd.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + shd.StockItemID + "'," +
"'" + shd.ModelNo + "'," +
shd.Quantity + "," +
"'" + shd.InwardDocumentID + "'," +
shd.InwardDocumentNo + "," +
"'" + shd.InwardDocumentDate.ToString("yyyy-MM-dd") + "',"
+ shd.StockReferenceNo + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "StockHoldingDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
return(status);
}
public Boolean updateSHHeaderAndDetail(stockholdingheader shh, stockholdingheader prevshh, List <stockholdingdetail> StockHoldingHeaderDetails)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update StockHoldingHeader set TemporaryNo='" + shh.TemporaryNo +
"',TemporaryDate='" + shh.TemporaryDate.ToString("yyyy-MM-dd") +
"',DocumentNo='" + shh.DocumentNo +
"',DocumentDate='" + shh.DocumentDate.ToString("yyyy-MM-dd") +
"', StoreLocationID='" + shh.StoreLocationID +
"', Remarks='" + shh.Remarks +
"', Comments='" + shh.Comments +
"', CommentStatus='" + shh.CommentStatus +
"', ForwarderList='" + shh.ForwarderList + "'" +
" where DocumentID='" + prevshh.DocumentID + "'" +
" and TemporaryNo=" + prevshh.TemporaryNo +
" and TemporaryDate='" + prevshh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "StockHoldingHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from StockHoldingDetail where DocumentID='" + prevshh.DocumentID + "'" +
" and TemporaryNo=" + prevshh.TemporaryNo +
" and TemporaryDate='" + prevshh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "StockHoldingDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (stockholdingdetail shd in StockHoldingHeaderDetails)
{
updateSQL = "insert into StockHoldingDetail " +
"(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,Quantity,InwardDocumentID,InwardDocumentNo,InwardDocumentdate,StockReferenceNo) " +
"values ('" + shd.DocumentID + "'," +
shd.TemporaryNo + "," +
"'" + shd.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
"'" + shd.StockItemID + "'," +
"'" + shd.ModelNo + "'," +
shd.Quantity + "," +
"'" + shd.InwardDocumentID + "'," +
shd.InwardDocumentNo + "," +
"'" + shd.InwardDocumentDate.ToString("yyyy-MM-dd") + "',"
+ shd.StockReferenceNo + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "StockHoldingDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
}
catch (Exception ex)
{
status = false;
}
return(status);
}
public List <stockholdingheader> getFilteredStockHoldingHeader(string userList, int opt, string userCommentStatusString)
{
stockholdingheader shh;
List <stockholdingheader> StockHoldingHeaderList = new List <stockholdingheader>();
try
{
//approved user comment status string
string acStr = "";
try
{
acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2;
}
catch (Exception ex)
{
acStr = "";
}
//-----
string query1 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," +
" StoreLocationID,StoreLocationName,Remarks,Comments," +
" CommentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,Status,DocumentStatus " +
" from ViewStockHoldingHeader" +
" where ((ForwardUser='******' and DocumentStatus between 2 and 98) " +
" or (CreateUser='******' and DocumentStatus=1)" +
" or (CommentStatus like '%" + userCommentStatusString + "%' and DocumentStatus between 1 and 98)) order by DocumentDate desc,DocumentID asc,DocumentNo desc";
string query2 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," +
" StoreLocationID,StoreLocationName,Remarks,Comments," +
" CommentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,Status,DocumentStatus " +
" from ViewStockHoldingHeader" +
" where ((createuser='******' and DocumentStatus between 2 and 98 ) " +
" or (ForwarderList like '%" + userList + "%' and DocumentStatus between 2 and 98 and ForwardUser <> '" + Login.userLoggedIn + "')" +
" or (commentStatus like '%" + acStr + "%' and DocumentStatus between 1 and 98)) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc";
string query3 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," +
" StoreLocationID,StoreLocationName,Remarks,Comments," +
" CommentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,Status,DocumentStatus " +
" from ViewStockHoldingHeader" +
" where ((createuser='******'" +
" or ForwarderList like '%" + userList + "%'" +
" or commentStatus like '%" + acStr + "%'" +
" or approveUser='******')" +
" and DocumentStatus = 99) order by DocumentDate desc,DocumentID asc,DocumentNo desc";
string query6 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate,DocumentNo,DocumentDate," +
" StoreLocationID,StoreLocationName,Remarks,Comments," +
" CommentStatus,CreateUser,ForwardUser,ApproveUser,CreatorName,CreateTime,ForwarderName,ApproverName,ForwarderList,Status,DocumentStatus " +
" from ViewStockHoldingHeader" +
" where DocumentStatus = 99 order by DocumentDate desc,DocumentID asc,DocumentNo desc";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 3:
query = query3;
break;
case 6:
query = query6;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
shh = new stockholdingheader();
shh.RowID = reader.GetInt32(0);
shh.DocumentID = reader.GetString(1);
shh.DocumentName = reader.GetString(2);
shh.TemporaryNo = reader.GetInt32(3);
shh.TemporaryDate = reader.GetDateTime(4);
shh.DocumentNo = reader.GetInt32(5);
shh.DocumentDate = reader.GetDateTime(6);
shh.StoreLocationID = reader.GetString(7);
shh.StoreLocationName = reader.GetString(8);
shh.Remarks = reader.GetString(9);
shh.Comments = reader.GetString(10);
if (!reader.IsDBNull(11))
{
shh.CommentStatus = reader.GetString(11);
}
else
{
shh.CommentStatus = "";
}
shh.CreateUser = reader.GetString(12);
shh.ForwardUser = reader.GetString(13);
shh.ApproveUser = reader.GetString(14);
shh.CreatorName = reader.GetString(15);
shh.CreateTime = reader.GetDateTime(16);
shh.ForwarderName = reader.GetString(17);
shh.ApproverName = reader.GetString(18);
if (!reader.IsDBNull(19))
{
shh.ForwarderList = reader.GetString(19);
}
else
{
shh.ForwarderList = "";
}
shh.Status = reader.GetInt32(20);
shh.DocumentStatus = reader.GetInt32(21);
StockHoldingHeaderList.Add(shh);
}
catch (Exception ex)
{
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Stock Header Details");
}
return(StockHoldingHeaderList);
}