public static async Task<List<double>> RetriveTaxesByType(string taxName) { List<double> taxList = new List<double>(); string sqlCmdString = "Select tax_amount From dbo.taxes Where tax_name=@tax_name"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); double tax; await Task.Run(() => { try { myDb.OpenConnection(); sqlCmd.Parameters.Add(new SqlParameter("@tax_name", taxName)); sqlAdapt.Fill(table); foreach (DataRow row in table.Rows) { tax = Convert.ToDouble(row["tax_amount"]); taxList.Add(tax); } } finally { sqlAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); return taxList; }
public static async Task<List<ChallanType>> RetrieveAllChallanTypes() { List<ChallanType> typeList = new List<ChallanType>(); string sqlCmdString = "Select * From dbo.challan_types"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); ChallanType challanType = null; await Task.Run(() => { try { myDb.OpenConnection(); sqlAdapt.Fill(table); foreach (DataRow row in table.Rows) { challanType = new ChallanType() { TypeName = (String)row["type_name"], IsPersist = true }; typeList.Add(challanType); } } finally { sqlAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); return typeList; }
public static async Task<Client> RetrieveByClientCode(string clientCode) { Client client = new Client(); string sqlCmdString = "Select * From dbo.clients Where client_code = @client_code"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); await Task.Run(() => { try { myDb.OpenConnection(); sqlCmd.Parameters.Add(new SqlParameter("@client_code", clientCode)); sqlAdapt.Fill(table); foreach (DataRow row in table.Rows) { client = new Client() { ClientCode = (String)row["client_code"], ClientName = (String)row["client_name"], TinNo = (String)row["tin_no"], MainAddress = (String)row["main_address"], PrimaryContactName = (String)row["primary_contact_name"], PrimaryContact = (String)row["primary_contact"], PrimaryEmail = (String)row["primary_email"], IsPersist = true }; } } finally { sqlAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); return client; }
public static async Task<List<OrderItem>> RetrieveItemsByOrderId(string orderId) { List<OrderItem> orderItemsList = new List<OrderItem>(); string sqlCmdString = "Select * From dbo.order_items Where order_id = @order_id"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); //SqlDataReader reader = null; SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); OrderItem orderItem; await Task.Run(async () => { try { myDb.OpenConnection(); sqlCmd.Parameters.Add(new SqlParameter("@order_id", orderId)); sqlAdapt.Fill(table); foreach (DataRow row in table.Rows) { orderItem = new OrderItem() { Qty = Convert.ToInt16(row["item_qty"]), RateAdjustment = Convert.ToInt32(row["rate_adjustment"]), IsPersist = true }; orderItem.Item = await StockItem.RetrieveBySkuCode((string)row["item_sku_code"]); orderItemsList.Add(orderItem); } } finally { sqlAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); return orderItemsList; }
public async Task UpdateInfo() { string sqlCmdString = "Update dbo.order_forms " + "Set order_approved = @order_approved, order_poc_no = @order_poc_no, order_hardcopy_url = @order_hardcopy_url " + "Where order_id = @order_id"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); if (OrderId == null) await AssignOrderId(); await Task.Run(() => { try { sqlCmd.Parameters.Add(new SqlParameter("@order_id", OrderId)); sqlCmd.Parameters.Add(new SqlParameter("@order_approved", IsApproved)); sqlCmd.Parameters.Add(new SqlParameter("@order_poc_no", (PocNo == null) ? "" : PocNo)); sqlCmd.Parameters.Add(new SqlParameter("@order_hardcopy_url", (OrderHardcopyUrl == null) ? "" : OrderHardcopyUrl)); sqlCmd.Transaction = myDb.InitiateTransaction(); sqlCmd.ExecuteNonQuery(); IsPersist = true; } catch (Exception ex) { myDb.EndTransaction(true); throw ex; } finally { myDb.EndTransaction(); } }); }
public async Task AssignOrderId() { string sqlCmdString = "Select Max(order_id) as max_order_id From dbo.order_forms"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); string maxCode; int newCodeNumeric; OrderId = "ORD"; await Task.Run(() => { try { myDb.OpenConnection(); maxCode = sqlCmd.ExecuteScalar() as string; if (maxCode != null) { newCodeNumeric = Convert.ToInt16(maxCode.Substring(3)) + 1; for (int i = 0; i < 7 - newCodeNumeric.ToString().Length; i++) { OrderId += "0"; } OrderId += newCodeNumeric; } else { OrderId = "ORD0000001"; } } finally { myDb.CloseConnection(); } }); }
static async Task<List<OrderForm>> RetrieveOrderFormsByCondition(String condition, params SqlParameter[] parameters) { List<OrderForm> ordersList = new List<OrderForm>(); string sqlCmdString = "Select * From dbo.order_forms " + condition; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); OrderForm form = null; await Task.Run(async () => { try { sqlCmd.Parameters.AddRange(parameters); myDb.OpenConnection(); sqlAdapt.Fill(table); foreach (DataRow row in table.Rows) { form = new OrderForm() { OrderId = (string)row["order_id"], Client = await Client.RetrieveByClientCode((string)row["client_code"]), OrderDate = (DateTime)row["order_date"], IsApproved = (bool)row["order_approved"], PocNo = (string)row["order_poc_no"], OrderHardcopyUrl = (string)row["order_hardcopy_url"], IsPersist = true }; await form.RetrieveOrderChallan(); ordersList.Add(form); } } finally { sqlAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); return ordersList; }
public static async Task<List<StockItem>> RetrieveAllStockItems() { List<StockItem> itemList = new List<StockItem>(); string sqlCmdString = "Select * From dbo.stock_items"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); StockItem item = null; await Task.Run(() => { try { myDb.OpenConnection(); sqlAdapt.Fill(table); foreach (DataRow row in table.Rows) { item = new StockItem() { ItemSkuCode = row["item_sku_code"] as String, ItemRate = Convert.ToInt32(row["item_rate"]), StockQty = Convert.ToInt32(row["stock_qty"]), ItemName = row["item_name"] as String, ItemDescription = row["item_description"] as String, IsPersist = true }; itemList.Add(item); } } finally { sqlAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); return itemList; }
public async Task PersistInfo() { if (!Validate()) throw new Exception("Client details not valid"); string sqlCmdString = "Insert Into dbo.challans (book_no, sl_no, challan_date, challan_type, order_id, freight_charge, vehicle_no, driver_mobile, consignment_note_no, transporter_name, vat, cst, special_note, received_amount) " + "Values (@book_no, @sl_no, @challan_date, @challan_type, @order_id, @freight_charge, @vehicle_no, @driver_mobile, @consignment_note_no, @transporter_name, @vat, @cst, @special_note, @received_amount)"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); await Task.Run(() => { try { sqlCmd.Parameters.Add(new SqlParameter("@book_no", BookNo)); sqlCmd.Parameters.Add(new SqlParameter("@sl_no", ChallanNo)); sqlCmd.Parameters.Add(new SqlParameter("@challan_date", ChallanDate)); sqlCmd.Parameters.Add(new SqlParameter("@challan_type", ChallanType.TypeName)); sqlCmd.Parameters.Add(new SqlParameter("@order_id", OrderForm.OrderId)); sqlCmd.Parameters.Add(new SqlParameter("@freight_charge", FreightCharge)); sqlCmd.Parameters.Add(new SqlParameter("@vehicle_no", VehicleNo)); sqlCmd.Parameters.Add(new SqlParameter("@driver_mobile", DriverMobile)); sqlCmd.Parameters.Add(new SqlParameter("@consignment_note_no", ConsignmentNoteNo)); sqlCmd.Parameters.Add(new SqlParameter("@transporter_name", TransporterName)); sqlCmd.Parameters.Add(new SqlParameter("@vat", Vat)); sqlCmd.Parameters.Add(new SqlParameter("@cst", Cst)); sqlCmd.Parameters.Add(new SqlParameter("@special_note", SpecialNote)); sqlCmd.Parameters.Add(new SqlParameter("@received_amount", ReceivedAmount)); sqlCmd.Transaction = myDb.InitiateTransaction(); sqlCmd.ExecuteNonQuery(); IsPersist = true; } catch (IOException ex) { GlobalAppStatus.AppStatus.StatusMessage(ex.Message, GlobalAppStatus.MessageType.ERROR); myDb.EndTransaction(true); } catch (SqlException ex) { GlobalAppStatus.AppStatus.StatusMessage(ex.Message, GlobalAppStatus.MessageType.ERROR); myDb.EndTransaction(true); } finally { myDb.EndTransaction(); } }); }
public async Task AssignChallanBookSerialNo() { string sqlCmdString = "Select Top 1 book_no, max(sl_no) as max_sl_no From dbo.challans Group By book_no Order By book_no Desc"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); BookNo = 1; ChallanNo = 0; await Task.Run(() => { try { myDb.OpenConnection(); sqlAdapt.Fill(table); foreach (DataRow row in table.Rows) { BookNo = (int)row["book_no"]; ChallanNo = (int)row["max_sl_no"]; } if (ChallanNo >= challansPerBook) { ChallanNo = 1; BookNo++; } else ChallanNo++; } finally { sqlAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); }
public async Task<int> RetrieveLatestStock() { string sqlCmdString = "Select stock_qty From dbo.stock_items Where item_sku_code = @item_sku_code"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); await Task.Run(() => { try { sqlCmd.Parameters.Add(new SqlParameter("@item_sku_code", ItemSkuCode)); myDb.OpenConnection(); StockQty = (int)sqlCmd.ExecuteScalar(); } finally { myDb.CloseConnection(); } }); return StockQty; }
async Task UpdateInfo() { if (!IsPersist || !IsUpdated) return; string sqlCmdString = "Update dbo.stock_items " + "Set item_rate = @item_rate, stock_qty = @stock_qty, item_name = @item_name, item_description = @item_description " + "Where item_sku_code = @item_sku_code"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); await Task.Run(() => { try { sqlCmd.Parameters.Add(new SqlParameter("@item_rate", ItemRate)); sqlCmd.Parameters.Add(new SqlParameter("@stock_qty", StockQty)); sqlCmd.Parameters.Add(new SqlParameter("@item_name", ItemName)); sqlCmd.Parameters.Add(new SqlParameter("@item_description", (ItemDescription == null) ? "" : ItemDescription)); sqlCmd.Parameters.Add(new SqlParameter("@item_sku_code", ItemSkuCode)); sqlCmd.Transaction = myDb.InitiateTransaction(); sqlCmd.ExecuteNonQuery(); IsUpdated = false; } catch(Exception ex) { myDb.EndTransaction(true); throw ex; } finally { myDb.EndTransaction(); } }); }
public async void PersistInfo() { if (!Validate()) throw new Exception("Stock Item details not valid"); if (IsPersist && IsUpdated) { await UpdateInfo(); return; } else if (IsPersist && !IsUpdated) return; string sqlCmdString = "Insert Into dbo.stock_items (item_sku_code, item_rate, stock_qty, item_name, item_description) " + "Values (@item_sku_code, @item_rate, @stock_qty, @item_name, @item_description)"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); await Task.Run(() => { try { sqlCmd.Parameters.Add(new SqlParameter("@item_sku_code", ItemSkuCode)); sqlCmd.Parameters.Add(new SqlParameter("@item_rate", ItemRate)); sqlCmd.Parameters.Add(new SqlParameter("@stock_qty", StockQty)); sqlCmd.Parameters.Add(new SqlParameter("@item_name", ItemName)); sqlCmd.Parameters.Add(new SqlParameter("@item_description", (ItemDescription == null) ? "" : ItemDescription)); sqlCmd.Transaction = myDb.InitiateTransaction(); sqlCmd.ExecuteNonQuery(); IsPersist = true; } catch(Exception ex) { myDb.EndTransaction(true); throw ex; } finally { myDb.EndTransaction(); } }); }
public static async Task<StockItem> RetrieveBySkuCode(String skuCode) { StockItem stockItem = new StockItem(); string sqlCmdString = "Select * From dbo.stock_items Where item_sku_code = @item_sku_code"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); await Task.Run(() => { try { myDb.OpenConnection(); sqlCmd.Parameters.Add(new SqlParameter("@item_sku_code", skuCode)); sqlAdapt.Fill(table); foreach (DataRow row in table.Rows) { stockItem = new StockItem() { ItemSkuCode = (string)row["item_sku_code"], ItemRate = Convert.ToInt32(row["item_rate"]), StockQty = Convert.ToInt32(row["stock_qty"]), ItemName = (string)row["item_name"], //ItemDescription = (String)(reader["item_description"] == null ? "" : reader["item_description"]), IsPersist = true }; } } finally { sqlAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); return stockItem; }
public async Task AssignClientCode() { string sqlCmdString = "Select Max(client_code) as max_client_code From dbo.clients"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); string maxCode; int newCodeNumeric; ClientCode = "CL"; await Task.Run(() => { try { myDb.OpenConnection(); maxCode = sqlCmd.ExecuteScalar() as string; if (maxCode != null) { newCodeNumeric = Convert.ToInt16(maxCode.Substring(2)) + 1; for (int i = 0; i < 4 - newCodeNumeric.ToString().Length; i++) { ClientCode += "0"; } ClientCode += newCodeNumeric; } else { ClientCode = "CL0001"; } } finally { myDb.CloseConnection(); } }); }
public static async Task<Challan> RetrieveChallanByOrder(OrderForm orderForm) { Challan challan = null; string sqlCmdString = "Select * From dbo.challans Where order_id = @order_id"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); SqlDataAdapter dataAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); await Task.Run(() => { try { myDb.OpenConnection(); sqlCmd.Parameters.Add(new SqlParameter("@order_id", orderForm.OrderId)); dataAdapt.Fill(table); if (table.Rows.Count == 1) { DataRow row = table.Rows[0]; challan = new Challan() { BookNo = Convert.ToInt32(row["book_no"]), ChallanNo = Convert.ToInt32(row["sl_no"]), ChallanDate = (DateTime)row["challan_date"], ChallanType = new ChallanType() { TypeName = (string)row["challan_type"] }, OrderForm = orderForm, FreightCharge = Convert.ToInt32(row["freight_charge"]), VehicleNo = (string)row["vehicle_no"], DriverMobile = (string)row["driver_mobile"], ConsignmentNoteNo = (string)row["consignment_note_no"], TransporterName = (string)row["transporter_name"], Vat = Convert.ToDouble(row["vat"]), Cst = Convert.ToDouble(row["cst"]), SpecialNote = row["special_note"] == DBNull.Value ? "" : (string)row["special_note"], ReceivedAmount = Convert.ToInt32(row["received_amount"]), IsPersist = true }; } } finally { dataAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); return challan; }
public async Task PersistInfo() { if (!Validate()) throw new Exception("Client details not valid"); string sqlCmdString = "Insert Into dbo.clients (client_code, client_name, tin_no, main_address, primary_contact_name, primary_contact, primary_email) " + "Values (@client_code, @client_name, @tin_no, @main_address, @primary_contact_name, @primary_contact, @primary_email)"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); if (ClientCode == null) await AssignClientCode(); await Task.Run(() => { try { sqlCmd.Parameters.Add(new SqlParameter("@client_code", ClientCode)); sqlCmd.Parameters.Add(new SqlParameter("@client_name", ClientName)); sqlCmd.Parameters.Add(new SqlParameter("@tin_no", TinNo)); sqlCmd.Parameters.Add(new SqlParameter("@main_address", MainAddress)); sqlCmd.Parameters.Add(new SqlParameter("@primary_contact", PrimaryContact)); sqlCmd.Parameters.Add(new SqlParameter("@primary_contact_name", PrimaryContactName)); sqlCmd.Parameters.Add(new SqlParameter("@primary_email", PrimaryEmail)); sqlCmd.Transaction = myDb.InitiateTransaction(); sqlCmd.ExecuteNonQuery(); IsPersist = true; } catch (IOException ex) { GlobalAppStatus.AppStatus.StatusMessage(ex.Message, GlobalAppStatus.MessageType.ERROR); myDb.EndTransaction(true); } catch (SqlException ex) { GlobalAppStatus.AppStatus.StatusMessage(ex.Message, GlobalAppStatus.MessageType.ERROR); myDb.EndTransaction(true); } finally { myDb.EndTransaction(); } }); }
public async void UpdateStockQty() { string sqlCmdString = "Update dbo.stock_items Set stock_qty = @stock_qty Where item_sku_code = @item_sku_code"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); await Task.Run(() => { try { sqlCmd.Parameters.Add(new SqlParameter("@item_sku_code", ItemSkuCode)); sqlCmd.Parameters.Add(new SqlParameter("@stock_qty", StockQty)); sqlCmd.Transaction = myDb.InitiateTransaction(); sqlCmd.ExecuteNonQuery(); IsPersist = true; } catch(Exception ex) { myDb.EndTransaction(true); throw ex; } finally { myDb.EndTransaction(); } }); }