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