public static DataTable GetAllDetailsByDate(string pDate)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataTable dt = new DataTable();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "sp_GetDetailedInventoryByDate @date ";
// cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@date", pDate);
// cmd.Parameters.AddWithValue("@todate", @pToDate);
try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr); dr.Close();
}
catch (Exception ex)
{
// ex.ToString();
throw;
}
finally
{
conn.Close();
}
return(dt);
}
public static DataTable GetSelectedIssueHeadDetails(String pStrIssueHeadName, string pFromDate, string pToDate)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataTable dt = new DataTable();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "sp_GetDetailedInventoryByIssueHeadandDates";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IssueHeadName1", pStrIssueHeadName);
cmd.Parameters.AddWithValue("@startdate", @pFromDate);
cmd.Parameters.AddWithValue("@todate", @pToDate);
try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr); dr.Close();
}
catch (Exception ex)
{
// ex.ToString();
throw;
}
finally
{
conn.Close();
}
return(dt);
}
public void UpdateNewIssuedItems(NewProperties issued, string sqlstatements)
{
SqlTransaction tr = null;
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
SqlCommand cmd = conn.CreateCommand();
// cmd.CommandText = "Insert into DeliveryItemsChallan(DeliveryItemsChallanID, IndentReference, IndentDate, ChallanDate, IndentingDivisionName, ChargeableHeadName,IsDeliveredTemporary,ModifiedBy, totalamount, vehiclenumber, receiverdesignation,Remarks)values(@DeliveryItemsChallanID, @IndentReference, @IndentDate, @ChallanDate, @IndentingDivisionName, @ChargeableHeadName,@IsDeliveredTemporary, @ModifiedBy, @totalamount, @vehiclenumber, @receiverdesignation,@remarks)";
cmd.CommandText = "Update DeliveryItemsChallan set IndentReference=@IndentReference, IndentDate=@IndentDate," +
" ChallanDate= @ChallanDate, IndentingDivisionName=@IndentingDivisionName, ChargeableHeadName= @ChargeableHeadName," +
"IsDeliveredTemporary=@IsDeliveredTemporary,ModifiedBy= @ModifiedBy, totalamount=@totalamount, vehiclenumber= @vehiclenumber, " +
"receiverdesignation=@receiverdesignation,Remarks=@remarks where DeliveryItemsChallanID=@DeliveryItemsChallanID ";
cmd.Parameters.AddWithValue("@DeliveryItemsChallanID", issued.challanNO);
cmd.Parameters.AddWithValue("@IndentReference", issued.indentNo);
cmd.Parameters.AddWithValue("@IndentDate", issued.indentDate);
cmd.Parameters.AddWithValue("@ChallanDate", issued.challanDate);
cmd.Parameters.AddWithValue("@IndentingDivisionName", issued.intendingDivision);
cmd.Parameters.AddWithValue("@ChargeableHeadName", issued.ChargeableHeadName);
cmd.Parameters.AddWithValue("@IsDeliveredTemporary", issued.IsDeliveredTemporary);
cmd.Parameters.AddWithValue("@ModifiedBy", issued.ModifiedBy);
cmd.Parameters.AddWithValue("@totalamount", issued.TotalAmount);
cmd.Parameters.AddWithValue("@vehiclenumber", issued.VehicleNumber);
cmd.Parameters.AddWithValue("@receiverdesignation", issued.ReceiverDesignation);
cmd.Parameters.AddWithValue("@remarks", issued.Remarks);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = sqlstatements;
try
{
using (conn)
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
cmd2.Transaction = tr;
cmd.ExecuteNonQuery();
if (sqlstatements != "")
{
cmd2.ExecuteNonQuery();
}
tr.Commit();
}
}
catch
{
// tr.Rollback();
throw;
}
finally
{
//conn.Close();
}
}
public static DataSet RetrieveActive_CHHeads_Items_Divisions()
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
// DataTable dt = new DataTable();
string cmd = "SELECT DISTINCT ChargeableHeadName FROM ChargeableHeads where status='A';";
//retrive item names
string cmd2 = "SELECT CONVERT(VARCHAR(10), itemid) + ' ' + unit as itemid_unit,itemname FROM Items where status='A'";
string cmd3 = "SELECT chargeableheadid, chargeableheadname FROM chargeableheads where status='A'";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd, conn);
//fill
dst.Tables.Add("IssueHeads"); dst.Tables.Add("Items"); dst.Tables.Add("ChargeableHeads");
adapter.Fill(dst.Tables[0]);
//2nd table
adapter = new SqlDataAdapter(cmd2, conn);
adapter.Fill(dst.Tables[1]);
//3rd table
adapter = new SqlDataAdapter(cmd3, conn);
adapter.Fill(dst.Tables[2]);
}
catch { throw; }
finally { conn.Close(); }
return(dst);
}
/// <summary>
/// get all items once received in received entry and not from master data items
/// </summary>
/// <returns></returns>
public static DataSet RetrieveReceivedItemsAndReceivedItemsDetails()
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
//
//string cmd = "SELECT ItemID, ItemName, unit, Quantity, Rate, amount, IssueHeadName FROM view_ReceivedItems_Tables";//v1
//v.2 - bringing itemname - rate - netactual balance
string cmd = "exec sp_ItemsEnquiryList";
//retrive item names
string cmd2 = "select distinct itemname, CONVERT(VARCHAR(10), itemid) + ' ' + unit as itemid_unit from ReceivedItemsDetails";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd, conn);
//fill
//v1- dst.Tables.Add("ReceivedItemsDetails");
dst.Tables.Add("ItemsEnquiryListTable");
dst.Tables.Add("Items");
adapter.Fill(dst.Tables[0]);
adapter = new SqlDataAdapter(cmd2, conn);
adapter.Fill(dst.Tables[1]);
}
catch { throw; }
return(dst);
}
/// <summary>
/// Save Received item oteo and items table data
/// </summary>
/// <param name="RecievedItemsOrderObject"></param>
/// <param name="sqlstatements"></param>
public void UpdateReceivedItemsDetails(properties RecievedItemsOrderObject, int originaloteoid)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
// stDate = DateTime.ParseExact(RecievedItemsOrderObject.Date, "dd-MM-yyyy", null).ToString("yyyy-MM-dd");
// edDate = DateTime.ParseExact(RecievedItemsOrderObject.SupplyDate, "dd-MM-yyyy", null).ToString("yyyy-MM-dd");
if (RecievedItemsOrderObject.Date.Contains("/"))
{
stDate = DateTime.ParseExact(RecievedItemsOrderObject.Date, @"d/M/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
}
else
{
stDate = DateTime.ParseExact(RecievedItemsOrderObject.Date, @"d-M-yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
}
if (RecievedItemsOrderObject.SupplyDate.Contains("/"))
{
edDate = DateTime.ParseExact(RecievedItemsOrderObject.SupplyDate, @"d/M/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
}
else
{
edDate = DateTime.ParseExact(RecievedItemsOrderObject.SupplyDate, @"d-M-yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
}
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "sp_UpdateReceivedItemsOTEO";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ReceivedItemsOTEOID", RecievedItemsOrderObject.ReceivedItemsOTEOID);
cmd.Parameters.AddWithValue("@ReceivedItemOTEODate", stDate);
cmd.Parameters.AddWithValue("@SupplyOrderReference", RecievedItemsOrderObject.SupplyOderRef);
cmd.Parameters.AddWithValue("@SupplyOrderDate", edDate);
cmd.Parameters.AddWithValue("@Supplier", RecievedItemsOrderObject.Supplier);
cmd.Parameters.AddWithValue("@ChargeableHeadName", RecievedItemsOrderObject.ChargeableHeadName);
cmd.Parameters.AddWithValue("@IssueHeadName", RecievedItemsOrderObject.IssueHeadName);
cmd.Parameters.AddWithValue("@TotalAmount", RecievedItemsOrderObject.TotalAmount);
cmd.Parameters.AddWithValue("@ModifiedBy", RecievedItemsOrderObject.ModifiedBy);
cmd.Parameters.AddWithValue("@OriginalReceivedItemsOTEOID", originaloteoid);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
conn.Close();
}
}
public void UpdateIssuedItems(properties issued, decimal orginalChallanID, string sqlstatements)
{
SqlTransaction tr = null;
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
//this will execute first
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Update DeliveryItemsChallan set DeliveryItemsChallanID =@DeliveryItemsChallanID, IndentReference=@IndentReference, IndentDate=@IndentDate, ChallanDate=@ChallanDate, IndentingDivisionName=@IndentingDivisionName, ChargeableHeadName=@ChargeableHeadName,IsDeliveredTemporary=@IsDeliveredTemporary,ModifiedBy=@ModifiedBy, vehiclenumber=@vehiclenumber, receiverdesignation=@receiverdesignation,Remarks=@Remarks where DeliveryItemsChallanID =@orginalChallanID";
cmd.Parameters.AddWithValue("@DeliveryItemsChallanID", issued.ChallanID);
cmd.Parameters.AddWithValue("@IndentReference", issued.IndentValue);
cmd.Parameters.AddWithValue("@IndentDate", issued.Date2);
cmd.Parameters.AddWithValue("@ChallanDate", issued.Date);
cmd.Parameters.AddWithValue("@IndentingDivisionName", issued.Division);
cmd.Parameters.AddWithValue("@ChargeableHeadName", issued.ChargeableHeadName);
cmd.Parameters.AddWithValue("@IsDeliveredTemporary", issued.IsDeliveredTemporary);
cmd.Parameters.AddWithValue("@ModifiedBy", issued.ModifiedBy);
cmd.Parameters.AddWithValue("@orginalChallanID", orginalChallanID);
cmd.Parameters.AddWithValue("@vehiclenumber", issued.VehicleNumber);
cmd.Parameters.AddWithValue("@receiverdesignation", issued.ReceiverDesignation);
cmd.Parameters.AddWithValue("@Remarks", issued.Remarks);
//2
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = sqlstatements;
SqlCommand cmd3 = conn.CreateCommand();
cmd3.CommandText = "update DeliveryItemsDetails set DeliveryItemsChallanID=@DeliveryItemsChallanID where DeliveryItemsChallanID =@orginalChallanID";
cmd3.Parameters.AddWithValue("@DeliveryItemsChallanID", issued.ChallanID);
cmd3.Parameters.AddWithValue("@orginalChallanID", orginalChallanID);
try
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
cmd2.Transaction = tr;
cmd3.Transaction = tr;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd3.ExecuteNonQuery();
tr.Commit();
}
catch
{
tr.Rollback();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// save in both tables
/// </summary>
/// <param name="issued">object of class containing the data for deliverychallan table(main table)</param>
/// <param name="sqlstatements">multiple sql statmenets insert to save in details secondary table</param>
///
public void SaveIssuedItems(properties issued, string sqlstatements)
{
SqlTransaction tr = null;
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
//this will execute first
// string stDate, edDate;
// stDate = DateTime.ParseExact(issued.Date, "dd/MM/yyyy", null).ToString("MM/dd/yyyy");
// edDate = DateTime.ParseExact(issued.Date2, "dd/MM/yyyy", null).ToString("MM/dd/yyyy");
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into DeliveryItemsChallan(DeliveryItemsChallanID, IndentReference, IndentDate, ChallanDate, IndentingDivisionName, ChargeableHeadName,IsDeliveredTemporary,ModifiedBy, totalamount, vehiclenumber, receiverdesignation,Remarks)values(@DeliveryItemsChallanID, @IndentReference, @IndentDate, @ChallanDate, @IndentingDivisionName, @ChargeableHeadName,@IsDeliveredTemporary, @ModifiedBy, @totalamount, @vehiclenumber, @receiverdesignation,@remarks)";
//cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@DeliveryItemsChallanID", issued.ChallanID);
cmd.Parameters.AddWithValue("@IndentReference", issued.IndentValue);
cmd.Parameters.AddWithValue("@IndentDate", issued.Date);
cmd.Parameters.AddWithValue("@ChallanDate", issued.Date2);
cmd.Parameters.AddWithValue("@IndentingDivisionName", issued.Division);
cmd.Parameters.AddWithValue("@ChargeableHeadName", issued.ChargeableHeadName);
cmd.Parameters.AddWithValue("@IsDeliveredTemporary", issued.IsDeliveredTemporary);
cmd.Parameters.AddWithValue("@ModifiedBy", issued.ModifiedBy);
cmd.Parameters.AddWithValue("@totalamount", issued.TotalAmount);
cmd.Parameters.AddWithValue("@vehiclenumber", issued.VehicleNumber);
cmd.Parameters.AddWithValue("@receiverdesignation", issued.ReceiverDesignation);
cmd.Parameters.AddWithValue("@remarks", issued.Remarks);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = sqlstatements;
try
{
using (conn)
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
cmd2.Transaction = tr;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tr.Commit();
}
}
catch
{
// tr.Rollback();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Save Received item oteo and items table data
/// </summary>
/// <param name="RecievedItemsOrderObject"></param>
/// <param name="sqlstatements"></param>
///
//SaveReceivedItemsDetails with 5 parameters
public void SaveReceivedItemsDetails(properties RecievedItemsOrderObject, string sqlstatements)
{
SqlTransaction tr = null;
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
//this will execute first
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "sp_InsertReceivedItemsAfterValidation";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ReceivedItemsOTEOID", RecievedItemsOrderObject.ReceivedItemsOTEOID);
cmd.Parameters.AddWithValue("@ReceivedItemOTEODate", RecievedItemsOrderObject.Date);
cmd.Parameters.AddWithValue("@SupplyOrderReference", RecievedItemsOrderObject.SupplyOderRef);
cmd.Parameters.AddWithValue("@SupplyOrderDate", RecievedItemsOrderObject.SupplyDate);
cmd.Parameters.AddWithValue("@Supplier", RecievedItemsOrderObject.Supplier);
cmd.Parameters.AddWithValue("@ChargeableHeadName", RecievedItemsOrderObject.ChargeableHeadName);
cmd.Parameters.AddWithValue("@IssueHeadName", RecievedItemsOrderObject.IssueHeadName);
cmd.Parameters.AddWithValue("@TotalAmount", RecievedItemsOrderObject.TotalAmount);
cmd.Parameters.AddWithValue("@ModifiedBy", RecievedItemsOrderObject.ModifiedBy);
// cmd.Parameters.AddWithValue("@XMLItems", XMLItems);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = sqlstatements;
try
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
cmd2.Transaction = tr;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tr.Commit();
}
catch
{
tr.Rollback();
throw;
}
finally
{
conn.Close();
}
}
public static DataSet RetrieveActiveIssueHeadsAndActiveItemsSeperately(out int maxOTEOID)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
//DataTable dt = new DataTable();
string cmd = "SELECT issueheadid, issueheadname FROM issueheads where status='A';";
string cmd2 = " select DISTINCT Items.itemid,CONVERT(VARCHAR(10), Items.itemid) + '$' + Items.unit + '$'+ ISNULL(ItemsRateMaster.IssueHeadName,'NOT') +'$'+ " +
" CONVERT(VARCHAR(10), ISNULL((MaxOrderNo)+1,1)) as itemid_unit,items.itemname " +
" FROM Items LEFT JOIN ItemsRateMaster ON ItemsRateMaster.itemname = items.itemname LEFT JOIN " +
" ISSUEHEADS ON ISSUEHEADS.IssueHeadName = ItemsRateMaster.IssueHeadName WHERE ITEMS.status='A'";
SqlCommand cmd3 = conn.CreateCommand();
cmd3.CommandText = "select max(ReceivedItemsOTEOID) from receiveditemsoteo";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd, conn);
//fill
dst.Tables.Add("IssueHeads");
dst.Tables.Add("Items");
// dst.Tables.Add("ItemsRateMaster");
adapter.Fill(dst.Tables[0]);
adapter = new SqlDataAdapter(cmd2, conn);
adapter.Fill(dst.Tables[1]);
// adapter = new SqlDataAdapter(cmd4, conn);
// adapter.Fill(dst.Tables[2]);
conn.Open();
if (cmd3.ExecuteScalar() != DBNull.Value)
{
maxOTEOID = Convert.ToInt32(cmd3.ExecuteScalar()) + 1;
}
else
{
maxOTEOID = 1;
}
}
catch { throw; }
finally { conn.Close(); }
return(dst);
}
// SaveReceivedItemsDetails with 4 parameters
#endregion
#region update to db received item oteo and received items details
/// <summary>
/// Save Received item oteo and items table data
/// </summary>
/// <param name="RecievedItemsOrderObject"></param>
/// <param name="sqlstatements"></param>
public void UpdateReceivedItemsDetails(properties RecievedItemsOrderObject, int originaloteoid, string sqlstatements)
{
SqlTransaction tr = null;
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
//this will execute first
stDate = DateTime.ParseExact(RecievedItemsOrderObject.Date, "dd-MM-yyyy", null).ToString("yyyy-MM-dd");
edDate = DateTime.ParseExact(RecievedItemsOrderObject.SupplyDate, "dd-MM-yyyy", null).ToString("yyyy-MM-dd");
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "sp_UpdateReceivedItemsOTEO";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ReceivedItemsOTEOID", RecievedItemsOrderObject.ReceivedItemsOTEOID);
cmd.Parameters.AddWithValue("@ReceivedItemOTEODate", stDate);
cmd.Parameters.AddWithValue("@SupplyOrderReference", RecievedItemsOrderObject.SupplyOderRef);
cmd.Parameters.AddWithValue("@SupplyOrderDate", edDate);
cmd.Parameters.AddWithValue("@Supplier", RecievedItemsOrderObject.Supplier);
cmd.Parameters.AddWithValue("@ChargeableHeadName", RecievedItemsOrderObject.ChargeableHeadName);
cmd.Parameters.AddWithValue("@IssueHeadName", RecievedItemsOrderObject.IssueHeadName);
cmd.Parameters.AddWithValue("@TotalAmount", RecievedItemsOrderObject.TotalAmount);
cmd.Parameters.AddWithValue("@ModifiedBy", RecievedItemsOrderObject.ModifiedBy);
cmd.Parameters.AddWithValue("@OriginalReceivedItemsOTEOID", originaloteoid);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = sqlstatements;
try
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
cmd2.Transaction = tr;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tr.Commit();
}
catch
{
tr.Rollback();
throw;
}
finally
{
conn.Close();
}
}
public static DataTable RetrieveCorrespondingActiveChargeableHeads(int intIssueHead)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataTable dt = new DataTable();
string cmdText = "SELECT chargeableheadid, chargeableheadname FROM chargeableheads where issueheadid=@issueheadid and status='A'";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, conn);
adapter.SelectCommand.Parameters.Add("@issueheadid", SqlDbType.SmallInt).Value = intIssueHead;
adapter.Fill(dt);
}
catch { throw; }
return(dt);
}
public static DataSet GetAllDetails()
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dt = new DataSet();
try
{
SqlDataAdapter dad = new SqlDataAdapter("sp_GetDetailedInventory", conn);
dad.Fill(dt);
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
conn.Close();
}
return(dt);
}
public static DataSet GetDetailsOfSelectedItemName(String pStrItemName)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dt = new DataSet();
try
{
SqlDataAdapter dad = new SqlDataAdapter("sp_GetDetailsByItemName '" + pStrItemName + "'", conn);
dad.Fill(dt);
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
conn.Close();
}
return(dt);
}
public static DataSet GetSelectedIssueHeadDetails(String pStrIssueHeadName)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dt = new DataSet();
try
{
SqlDataAdapter dad = new SqlDataAdapter("sp_IssueHeadWiseDetailedInventory '" + pStrIssueHeadName + "'", conn);
dad.Fill(dt);
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
conn.Close();
}
return(dt);
}
public static DataSet RetrieveActiveIssueHeadsAndActiveItemsSeperatelyOnIssueItemChanged(string myIssueHead)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
string cmd2 = " select DISTINCT Items.itemid,CONVERT(VARCHAR(10), Items.itemid) + ' ' + Items.unit from items WHERE ITEMS.status='A' ";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd2, conn);
dst.Tables.Add("Items");
adapter.Fill(dst.Tables[0]);
}
catch { throw; }
finally { conn.Close(); }
return(dst);
}
public static DataSet RetrieveActive_IssueHeads_Items_ChargeableHeads()
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
// DataTable dt = new DataTable();
string cmd = "SELECT issueheadid, issueheadname FROM issueheads where status='A';";
//retrive item names
// string cmd2 = "SELECT CONVERT(VARCHAR(10), itemid) + ' ' + unit as itemid_unit,itemname FROM Items where status='A'";
//string cmd2 = " select DISTINCT Items.itemid,CONVERT(VARCHAR(10), Items.itemid) + '$' + Items.unit + '$'+ ISNULL(ItemsRateMaster.IssueHeadName,'NOT') +'$'+ " +
// " CONVERT(VARCHAR(10), ISNULL((MaxOrderNo)+1,1)) as itemid_unit, itemname " +
// " FROM Items left JOIN itemsrateMaster ON itemsrateMaster.itemid = items.itemid LEFT JOIN " +
// " ISSUEHEADS ON ISSUEHEADS.IssueHeadName = itemsrateMaster.IssueHeadName WHERE ITEMS.status='A'";
string cmd2 = "select itemname, CONVERT(VARCHAR(10), itemid) + ' ' + unit as itemid_unit from items";
string cmd3 = "SELECT chargeableheadid, chargeableheadname FROM chargeableheads where status='A'";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd, conn);
//fill
dst.Tables.Add("IssueHeads");
dst.Tables.Add("Items");
dst.Tables.Add("ChargeableHeads");
adapter.Fill(dst.Tables[0]);
//2nd table
adapter = new SqlDataAdapter(cmd2, conn);
adapter.Fill(dst.Tables[1]);
//3rd table
adapter = new SqlDataAdapter(cmd3, conn);
adapter.Fill(dst.Tables[2]);
}
catch { throw; }
finally { conn.Close(); }
return(dst);
}
public static DataSet RetrieveAllItems()
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
//reterive issue HEadNames
string cmd = "select ItemName,issueheadname,sum(quantity) as QTY,RTRIM (issueheadname)+ ' = '+ CONVERT(VARCHAR(12),sum(quantity)) " +
"as issuableQuantity from ItemsRateSecondary group by itemname,issueheadname having sum(quantity)>0.00 order by itemname,issueheadname ";
//retrive item names
string cmd2 = "select itemname, CONVERT(VARCHAR(10), itemid) + ' ' + unit as itemid_unit from items order by Itemname";
string cmd3 = "select itemname,issueheadname,Rate,OrderNo,Quantity,cast((Rate*Quantity)as decimal(10,2)) AS AMT from ItemsRateSecondary IT2 where quantity>0 order by rate, OrderNo";
string cmd4 = "select sum(quantity) as TotQtyAvailable, itemname,IssueHeadName from ItemsRateSecondary group by itemname,IssueHeadName having sum(quantity)>0.00 ";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd2, conn);
conn.Close();
dst.Tables.Add("Items");
adapter.Fill(dst.Tables["Items"]);
adapter.SelectCommand.CommandText = cmd;
dst.Tables.Add("itemRatesSecondery");
adapter.Fill(dst.Tables["itemRatesSecondery"]);
adapter.SelectCommand.CommandText = cmd3;
dst.Tables.Add("IT2");
adapter.Fill(dst.Tables["IT2"]);
adapter.SelectCommand.CommandText = cmd4;
dst.Tables.Add("IT3");
adapter.Fill(dst.Tables["IT3"]);
}
catch { throw; }
return(dst);
}
public static string GetDivisionNameofReceivedTransformer(int receivedtransformerid)
{
string divisionname = "";
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select division from transformer_receipts where challanno=(select challanno from transformer_receiptsdetails where receivedtransformerid = @receivedtransformerid)";
cmd.Parameters.AddWithValue("@receivedtransformerid", receivedtransformerid);
try
{
conn.Open();
if (cmd.ExecuteScalar() != DBNull.Value)
{
divisionname = cmd.ExecuteScalar().ToString();
}
}
catch { throw; }
finally { conn.Close(); }
return(divisionname);
}
/// <summary>
/// for receieved entry page, get active chargeable heads , corresponding issue head and active items seperately
/// </summary>
/// <returns></returns>
/// public static void RetrieveActiveIssueHeadsAndActiveItemsSeperately(out int maxOTEOID)
public static DataSet RetrieveActiveChargeableHeadsAndActiveItemsSeperately(out int maxOTEOID)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
// DataTable dt = new DataTable();
// string cmd = "SELECT ChargeableHeadName, (CONVERT(VARCHAR(10), chargeableheadid) + '*' + issueheadname + '*' + CONVERT(VARCHAR(10), issueheadid)) as chheadid_issueheadname_id FROM ChargeableHeads where status='A';";
string cmd = "SELECT ChargeableHeadName, chargeableheadid FROM ChargeableHeads where status='A';";
//retrive item names
string cmd2 = "SELECT CONVERT(VARCHAR(10), itemid) + ' ' + unit as itemid_unit,itemname FROM Items where status='A'";
SqlCommand cmd3 = conn.CreateCommand();
cmd3.CommandText = "select max(ReceivedItemsOTEOID) from receiveditemsoteo";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd, conn);
//fill
dst.Tables.Add("ChargeableHeads"); dst.Tables.Add("Items");
adapter.Fill(dst.Tables[0]);
adapter = new SqlDataAdapter(cmd2, conn);
adapter.Fill(dst.Tables[1]);
conn.Open();
if (cmd3.ExecuteScalar() != DBNull.Value)
{
maxOTEOID = Convert.ToInt32(cmd3.ExecuteScalar()) + 1;
}
else
{
maxOTEOID = 1;
}
}
catch { throw; }
finally { conn.Close(); }
return(dst);
}
public void SaveTransformerReceiptEntry(string sqlstatement1, string sqlstatements)
{
SqlTransaction tr = null;
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
//this will execute first
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sqlstatement1;
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = sqlstatements;
try
{
using (conn)
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
cmd2.Transaction = tr;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tr.Commit();
}
}
catch
{
// tr.Rollback();
throw;
}
finally
{
conn.Close();
}
}
public static void WriteLog(bool isError, String pStrEmpID, String pStrEvent, string previousPageURL)
{
SqlConnection conn = null;
try
{
conn = new SqlConnection(AppConns.GetConnectionString());
}
catch
{
throw;
}
SqlCommand cmdlog = conn.CreateCommand();
//insert into table
cmdlog.CommandText = "insert LOGS (ISERROR, EVENT,PREVIOUSPAGEURL, SYSTEMUSERID,MODON) values (@ISERROR, @EVENT,@PREVIOUSPAGEURL,@SYSTEMUSERID,@MODON)";
cmdlog.Parameters.AddWithValue("@USERID", isError);
cmdlog.Parameters.AddWithValue("@EVENT", pStrEvent);
cmdlog.Parameters.AddWithValue("@PREVIOUSPAGEURL", previousPageURL);
cmdlog.Parameters.AddWithValue("@SYSTEMUSERID", pStrEmpID);
cmdlog.Parameters.AddWithValue("@MODON", System.DateTime.Now);
try
{
conn.Open();
cmdlog.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
conn.Close();
}
}
public static DataSet RetrieveAllItems()
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
//reterive issue HEadNames
string cmd = "select distinct ItemName,issueheadname from ItemsRateSecondary ";
//retrive item names
string cmd2 = "select itemname, CONVERT(VARCHAR(10), itemid) + ' ' + unit as itemid_unit from items";
string cmd3 = "select itemname,issueheadname,rate, max(OrderNo) as MaxOrderNo from ItemsRateSecondary IT2 group by itemname,issueheadname,rate order by MaxOrderNo desc";
string cmd4 = "select sum(quantity) TotQtyAvailable, itemname,IssueHeadName from ItemsRateSecondary group by itemname,IssueHeadName";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd2, conn);
dst.Tables.Add("Items");
adapter.Fill(dst.Tables["Items"]);
adapter.SelectCommand.CommandText = cmd;
dst.Tables.Add("itemRatesSecondery");
adapter.Fill(dst.Tables["itemRatesSecondery"]);
adapter.SelectCommand.CommandText = cmd3;
dst.Tables.Add("IT2");
adapter.Fill(dst.Tables["IT2"]);
adapter.SelectCommand.CommandText = cmd4;
dst.Tables.Add("IT3");
adapter.Fill(dst.Tables["IT3"]);
conn.Close();
}
catch { throw; }
return(dst);
}
public static DataSet RetrieveActiveIssueHeadsAndActiveItemsSeperatelyOnIssueItemChanged(string myIssueHead)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
string cmd2 = " select DISTINCT Items.itemid,CONVERT(VARCHAR(10), Items.itemid) + ' ' + Items.unit + ' ' + " +
" CONVERT(VARCHAR(10), ISNULL((MaxOrderNo)+1,1)) as itemid_unit,Items.itemname " +
" FROM Items LEFT JOIN ItemsRateMaster ON ItemsRateMaster.itemname = items.itemname LEFT JOIN " +
" ISSUEHEADS ON ISSUEHEADS.IssueHeadName = ItemsRateMaster.IssueHeadName WHERE ITEMS.status='A' and IssueHeads.IssueHeadName='" + myIssueHead + "'";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd2, conn);
dst.Tables.Add("Items");
adapter.Fill(dst.Tables[0]);
}
catch { throw; }
finally { conn.Close(); }
return(dst);
}
public static DataSet RetrieveActiveIssueHeadsAndActiveItemsSeperately(out int maxOTEOID)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
DataSet dst = new DataSet();
//DataTable dt = new DataTable();
string cmd = "SELECT issueheadid, issueheadname FROM issueheads where status='A';";
//retrive item names
//commented by bisu
string cmd2 = "SELECT CONVERT(VARCHAR(10), itemid) + ' ' + unit as itemid_unit,itemname FROM Items where status='A'";
//bisu writes cmd4
//this code is ok
//this code is tested and is ok
/* string cmd2 = "SELECT CONVERT(VARCHAR(10), Items.itemid) + ' ' + unit + ' ' + " +
* " CONVERT(VARCHAR(10), ISNULL((OrderNo)+1,1)) as itemid_unit, itemname " +
* " FROM Items LEFT JOIN ItemsRateMaster ON Items.itemid=ItemsRateMaster.itemId WHERE status='A' " +
* " order by Items.itemid,OrderNo";
*/
/* string cmd2 = " select DISTINCT Items.itemid,CONVERT(VARCHAR(10), Items.itemid) + ' ' + Items.unit + ' ' + "+
* " CONVERT(VARCHAR(10), ISNULL((MaxOrderNo)+1,1)) as itemid_unit, itemname "+
* " FROM Items LEFT JOIN ItemsRateMaster ON ItemsRateMaster.itemid ="+
* " (select distinct ItemsRateMaster.itemid from "+
* " ItemsRateMaster where items.itemid =ItemsRateMaster.itemid) ";
*
* */
// string cmd2 = "SELECT CONVERT(VARCHAR(10), Items.ItemID) + ' ' + unit + ' ' + " +
// " CONVERT(VARCHAR(10), ISNULL((OrderNo)+1,1)) as itemid_unit,itemname " +
//" FROM Items LEFT JOIN (select OrderNo from ItemsRateMaster) " +
//" AS ItemsRateMaster ON (Items.ItemID=ItemsRateMaster.ItemId) WHERE status='A' ";
// commented by bisu
// string cmd4 = "SELECT ItemId,IssueHeadNo,OrderNo from ItemsRateMaster";
//string cmd4 = "SELECT ItemsRateMaster.ItemId,IssueHeadNo,OrderNo as order1 from ItemsRateMaster,Items,issueheads where Items.itemid=ItemsRateMaster.ItemId and issueheads.issueheadid=ItemsRateMaster.IssueHeadNo and issueheads.status='A' and Items.status='A'";
// string cmd2 = " select DISTINCT Items.itemid,CONVERT(VARCHAR(10), Items.itemid) + ' ' + Items.unit as itemid_unit, itemname " +
// FROM Items WHERE ITEMS.status='A'";
SqlCommand cmd3 = conn.CreateCommand();
cmd3.CommandText = "select max(ReceivedItemsOTEOID) from receiveditemsoteo";
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd, conn);
//fill
dst.Tables.Add("IssueHeads");
dst.Tables.Add("Items");
// dst.Tables.Add("ItemsRateMaster");
adapter.Fill(dst.Tables[0]);
adapter = new SqlDataAdapter(cmd2, conn);
adapter.Fill(dst.Tables[1]);
// adapter = new SqlDataAdapter(cmd4, conn);
// adapter.Fill(dst.Tables[2]);
conn.Open();
if (cmd3.ExecuteScalar() != DBNull.Value)
{
maxOTEOID = Convert.ToInt32(cmd3.ExecuteScalar()) + 1;
}
else
{
maxOTEOID = 1;
}
}
catch { throw; }
finally { conn.Close(); }
return(dst);
}
