private static List <Item> GetSourceItems(OracleConnection conn, string schemeName)
{
List <Item> output = new List <Item>();
string sql = "SELECT * FROM WV_BOQ_DATA WHERE SCHEME_NAME='" + schemeName + "'";
using (OracleDataReader dr = UtilityDb2.GetDataReader(sql, conn))
{
while (dr.Read())
{
Item item = new Item();
item.ContractNo = dr["CONTRACT_NO"].ToString();
item.ItemNo = dr["ITEM_NO"].ToString();
item.Quantity = Convert.ToSingle(dr["PU_QTY"]);
string isposp = dr["ISP_OSP"].ToString();
if (isposp.ToUpper() == "ISP")
{
item.CotRt = "COT";
}
else if (isposp.ToUpper() == "OSP")
{
item.CotRt = "RT";
}
output.Add(item);
}
}
return(output);
}
public bool GenerateBOQItems(string schemeName, OracleConnection conn, ref string errorMessage)
{
try
{
WebView.BOQNrmService.nrmboqClient svc = new WebView.BOQNrmService.nrmboqClient();
string errorCode = "";
string errorDesc = "";
BOQItemList itemList = svc.generateBOQItems(out errorCode, out errorDesc, schemeName);
List <Item> expandedList = ExpandWebServiceItems(itemList);
if (string.IsNullOrEmpty(errorCode)) // no errors
{
// insert into db
string sql = "DELETE FROM WV_ISP_TEMP WHERE SCHEME_NAME='" + schemeName + "'";
UtilityDb2.ExecuteSql(sql, conn);
using (UtilityDb2 db = new UtilityDb2())
{
db.connection = conn;
db.PrepareInsert("WV_ISP_TEMP");
foreach (Item item in expandedList)
{
DataRow row = db.Insert(null);
row["SCHEME_NAME"] = item.schemeName;
row["CONTRACT_NO"] = item.contractNo;
row["ITEM_NO"] = item.itemNo;
int quantity = 0;
if (int.TryParse(item.quantity, out quantity))
{
row["QUANTITY"] = quantity.ToString();
}
row["FEATURE_STATE"] = item.featureState;
db.Insert(row);
}
db.EndInsert();
}
return(true);
}
else
{
errorMessage = errorCode + ": " + errorDesc;
return(true);
}
}
catch (Exception ex)
{
errorMessage = "Exception: " + ex.Message;
return(false);
}
}
private static List <Package> GetPackages(OracleConnection conn,
Item originalItem, string [] userSpecifiedPackages)
{
List <Package> packages = new List <Package>();
// create packages according to the quantity of the item.
for (int i = 0; i < originalItem.Quantity; i++)
{
Package package = new Package();
package.OriginalItem = originalItem;
packages.Add(package);
}
// find items for the packages
bool hasItems = false;
string sql = "SELECT * FROM WV_PACKAGE_MAST WHERE MANDATORY='Y' AND CONTRACT_NO='" +
originalItem.ContractNo + "' AND PACKAGE_ITEM_NO='" + originalItem.ItemNo + "'";
using (OracleDataReader dr = UtilityDb2.GetDataReader(sql, conn))
{
while (dr.Read())
{
hasItems = true;
foreach (Package package in packages)
{
Package.PackageItem specItem = new Package.PackageItem();
specItem.ItemNo = dr["ITEM_NO"].ToString();
specItem.Quantity = Convert.ToSingle(dr["ITEM_QTY"]);
specItem.Mandatory = dr["MANDATORY"].ToString().ToUpper() == "Y";
specItem.CotRt = dr["COT-RT"].ToString();
specItem.ItemType = dr["TYPE"].ToString();
specItem.ContractNo = dr["CONTRACT_NO"].ToString();
package.SpecifiedItems.Add(specItem);
}
}
}
// return the list of created packages if items were found
// otherwise return null
foreach (string strPackageItemNo in userSpecifiedPackages)
{
if (strPackageItemNo.Trim() == originalItem.ItemNo)
{
return(packages);
}
}
return(null);
}
public static void Run(bool writeOutputFile,
OracleConnection conn,
string schemeName,
string packagesToCreate,
out List <Package> packages,
out List <Item> individualItems)
{
packages = new List <Package>();
individualItems = new List <Item>();
List <Item> source = GetSourceItems(conn, schemeName);
//List<Item> source = TestGetSourceItems(conn, schemeName);
// copy source items into individual items
foreach (Item item in source)
{
Item copyItem = item.Copy();
individualItems.Add(copyItem);
}
// create packages, one package per contract number
char[] commaDelimiter = { ',' };
string[] arPackagesToCreate = packagesToCreate.Split(commaDelimiter);
Dictionary <string, string> contractNos = new Dictionary <string, string>();
foreach (Item item in source)
{
string contractNo = item.ContractNo;
if (!contractNos.ContainsKey(contractNo))
{
foreach (string packageItemNo in arPackagesToCreate)
{
CreateItem(individualItems, contractNo, packageItemNo, 1, "");
}
contractNos[contractNo] = "x"; // does not matter what you keep there. this is just to indicate that the contract has been catered for
}
}
// extract packages out of the individualItems
List <Item> toRemove = new List <Item>();
foreach (Item item in individualItems)
{
List <Package> createdPackages = GetPackages(conn, item, arPackagesToCreate);
if (createdPackages != null)
{
packages.AddRange(createdPackages);
toRemove.Add(item);
}
}
foreach (Item item in toRemove)
{
individualItems.Remove(item); // remove the items that have been identified as packages
}
// by this time, we have separated the items into packages and individual items
// so, we assign the individual items into the packages
foreach (Package package in packages)
{
if (package.CanBeFilled(individualItems))
{
package.Fill(individualItems);
}
}
// remove items that have been taken by the packages
toRemove = new List <Item>();
foreach (Item item in individualItems)
{
if (item.Quantity == 0)
{
toRemove.Add(item);
}
}
foreach (Item item in toRemove)
{
individualItems.Remove(item);
}
// remove incomplete packages from list
List <Package> completePackages = new List <Package>();
foreach (Package package in packages)
{
if (package.HasBeenFilled)
{
completePackages.Add(package);
}
}
packages = completePackages;
//if (writeOutputFile)
// WriteOutput("Output.txt", source, packages, individualItems, packagesToCreate);
// delete the source items from database
string deletesql = "DELETE FROM WV_BOQ_DATA WHERE SCHEME_NAME='" + schemeName + "'";
UtilityDb2.ExecuteSql(deletesql, conn);
}
