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