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