public bool RB_Akashic_Extraction(string filePath, int pubid, string FileName, string MediaType,
System.Windows.Forms.Label lbl_Extraction,
System.Windows.Forms.Label lbl_Insert,
System.Windows.Forms.Label lbl_Message)
{
bool result = true;
string Step = "";
try
{
lbl_Extraction.BackColor = System.Drawing.Color.Yellow;
lbl_Extraction.Refresh();
System.Windows.Forms.Application.DoEvents();
#region 'DataTable Creation'
DataTable dt_MainTitles = new DataTable("MainTitles");
#region 'Columns Declaration'
dt_MainTitles.Columns.Add("MetaDataID", typeof(int));
dt_MainTitles.Columns.Add("ProductID", typeof(int));
dt_MainTitles.Columns.Add("RowCnt", typeof(int));
dt_MainTitles.Columns.Add("ISBN", typeof(string));
dt_MainTitles.Columns.Add("Title", typeof(string));
dt_MainTitles.Columns.Add("SubTitle", typeof(string));
dt_MainTitles.Columns.Add("Description", typeof(string));
dt_MainTitles.Columns.Add("PageCount", typeof(string));
dt_MainTitles.Columns.Add("Unabridged", typeof(string));
dt_MainTitles.Columns.Add("Language", typeof(string));
dt_MainTitles.Columns.Add("BISAC", typeof(string));
dt_MainTitles.Columns.Add("PublishDate", typeof(string));
dt_MainTitles.Columns.Add("ReleaseDate", typeof(string));
dt_MainTitles.Columns.Add("RightsCountry", typeof(string));
dt_MainTitles.Columns.Add("SeriesName", typeof(string));
dt_MainTitles.Columns.Add("SeriesPositionNumber", typeof(string));
dt_MainTitles.Columns.Add("Status", typeof(string));
dt_MainTitles.Columns.Add("PublisherName", typeof(string));
#endregion
DataTable dt_Price = new DataTable("Price");
#region 'Columns Declaration'
dt_Price.Columns.Add("MetaDataID", typeof(int));
dt_Price.Columns.Add("ProductID", typeof(int));
dt_Price.Columns.Add("RowCnt", typeof(int));
dt_Price.Columns.Add("PriceType_j148", typeof(string));
dt_Price.Columns.Add("LibraryPrice_j151", typeof(string));
dt_Price.Columns.Add("CurrencyCode_j152", typeof(string));
dt_Price.Columns.Add("j261", typeof(string));
#endregion
DataTable dt_SalesRights = new DataTable("SalesRights");
#region 'Columns Declaration'
dt_SalesRights.Columns.Add("MetaDataID", typeof(int));
dt_SalesRights.Columns.Add("ProductID", typeof(int));
dt_SalesRights.Columns.Add("RowCnt", typeof(int));
dt_SalesRights.Columns.Add("SalesRightsTypeCode_b089", typeof(string));
dt_SalesRights.Columns.Add("RightsCountry_b090", typeof(string));
dt_SalesRights.Columns.Add("RightsTerritory_b388", typeof(string));
#endregion
DataTable dt_contributor = new DataTable("Contributor");
#region 'Columns Declaration'
dt_contributor.Columns.Add("MetaDataID", typeof(int));
dt_contributor.Columns.Add("ProductID", typeof(int));
dt_contributor.Columns.Add("RowCnt", typeof(int));
dt_contributor.Columns.Add("Contribs_SequenceNumber_b034", typeof(string));
dt_contributor.Columns.Add("Contribs_ContribCode_b035", typeof(string));
dt_contributor.Columns.Add("Contribs_FNF_b036", typeof(string));
dt_contributor.Columns.Add("Contribs_LNF_b037", typeof(string));
dt_contributor.Columns.Add("Contribs_Prefix_b038", typeof(string));
dt_contributor.Columns.Add("Contribs_FirstName_b039", typeof(string));
dt_contributor.Columns.Add("Contribs_LastName_b040", typeof(string));
dt_contributor.Columns.Add("Contribs_CorporateAuthors_b047", typeof(string));
#endregion
#endregion
DataTable dt_Excel = new DataTable();
SQLFunction sqlfunction = new SQLFunction();
dt_Excel = sqlfunction.ReadExcel(filePath, dt_Excel);
if (
CheckIfColumnExist(dt_Excel, "publisher") &&
CheckIfColumnExist(dt_Excel, "isbn") &&
CheckIfColumnExist(dt_Excel, "title") &&
CheckIfColumnExist(dt_Excel, "subtitle") &&
CheckIfColumnExist(dt_Excel, "author(s)") &&
CheckIfColumnExist(dt_Excel, "illustrator(s)") &&
CheckIfColumnExist(dt_Excel, "unabridged") &&
CheckIfColumnExist(dt_Excel, "bisac code") &&
CheckIfColumnExist(dt_Excel, "publish date") &&
CheckIfColumnExist(dt_Excel, "release date") &&
CheckIfColumnExist(dt_Excel, "page count") &&
CheckIfColumnExist(dt_Excel, "library price") &&
CheckIfColumnExist(dt_Excel, "currency type") &&
CheckIfColumnExist(dt_Excel, "distribution territory") &&
CheckIfColumnExist(dt_Excel, "specific countries") &&
CheckIfColumnExist(dt_Excel, "description") &&
CheckIfColumnExist(dt_Excel, "language (3 digit country code)") &&
CheckIfColumnExist(dt_Excel, "series name") &&
CheckIfColumnExist(dt_Excel, "series position number") &&
CheckIfColumnExist(dt_Excel, "status")
)
{
int MetaDataID = InsertMetaData_Info(pubid, FileName, MediaType, "RB");
if (MetaDataID > 0)
{
for (int i = 0; i < dt_Excel.Rows.Count; i++)
{
#region 'MainTitles'
Step = "MainTitles";
dt_MainTitles = MainTitles(dt_MainTitles, dt_Excel, MetaDataID, i);
#endregion
#region 'Price'
Step = "Price";
dt_Price = Price(dt_Price, dt_Excel, MetaDataID, i);
#endregion
#region 'SalesRights'
Step = "SalesRights";
dt_SalesRights = SalesRights(dt_SalesRights, dt_Excel, MetaDataID, i);
#endregion
#region 'Contributor'
Step = "Contributor";
dt_contributor = Contributor(dt_contributor, dt_Excel, MetaDataID, i);
#endregion
}
}
}
lbl_Extraction.BackColor = System.Drawing.Color.Green;
lbl_Extraction.Refresh();
System.Windows.Forms.Application.DoEvents();
lbl_Insert.BackColor = System.Drawing.Color.Yellow;
lbl_Insert.Refresh();
System.Windows.Forms.Application.DoEvents();
#region 'Insert the Data into the SQL Table'
int count = 4;
result = InsertRecords(dt_MainTitles, "RB");
Insertion_Label(lbl_Insert, count);
count--;
if (result)
{
result = InsertRecords(dt_Price, "RB");
Insertion_Label(lbl_Insert, count);
}
count--;
if (result)
{
result = InsertRecords(dt_SalesRights, "RB");
Insertion_Label(lbl_Insert, count);
}
count--;
if (result)
{
result = InsertRecords(dt_contributor, "RB");
Insertion_Label(lbl_Insert, count);
}
count--;
if (result)
{
lbl_Insert.BackColor = System.Drawing.Color.Green;
lbl_Insert.Refresh();
System.Windows.Forms.Application.DoEvents();
}
#endregion
}
catch (Exception ex)
{
result = false;
SQLFunction sqlfnction = new SQLFunction();
sqlfnction.Insert_ErrorLog(sqlfnction.GetConnectionString("RB"), "Error at " + Step + " : " + ex.ToString());
}
return(result);
}
private void btn_Upload_Click(object sender, EventArgs e)
{
lbl_failurerows.Text = "";
lbl_failure_rowsexists.Text = "";
string failure_rows = "Unable to Upload Records (Rows No): ";
string failure_existsrows = "Records already Exists (Row No): ";
DataTable dt_Excel = new DataTable();
SQLFunction sqlfunction = new SQLFunction();
dt_Excel = sqlfunction.ReadExcel(str_Company, @"\\Pfingestion01\Incoming\TitleManagement\Metadata_Prod\Reports\RoyaltyDetails\RRD.xlsx");
if (
CheckIfColumnExist(dt_Excel, "Parent_PublisherName") &&
CheckIfColumnExist(dt_Excel, "Imprint_PublisherName") &&
CheckIfColumnExist(dt_Excel, "Imprint_Publisher_AccountNo") &&
CheckIfColumnExist(dt_Excel, "d_agent_code") &&
CheckIfColumnExist(dt_Excel, "d_royalty_percent")
)
{
for (int i = 0; i < dt_Excel.Rows.Count; i++)
{
string Parent_PublisherName = dt_Excel.Rows[i]["Parent_PublisherName"].ToString();
string Imprint_PublisherName = dt_Excel.Rows[i]["Imprint_PublisherName"].ToString();
string Imprint_Publisher_AccountNo = dt_Excel.Rows[i]["Imprint_Publisher_AccountNo"].ToString();
string d_agent_code = dt_Excel.Rows[i]["d_agent_code"].ToString();
string d_royalty_percent = dt_Excel.Rows[i]["d_royalty_percent"].ToString();
if (Parent_PublisherName.Trim().Length == 0 ||
Imprint_PublisherName.Trim().Length == 0 ||
Imprint_Publisher_AccountNo.Trim().Length == 0 ||
d_agent_code.Trim().Length == 0 ||
d_royalty_percent.Trim().Length == 0)
{
failure_rows += ", " + (i + 2);
}
else
{
int count = sqlfunction.CheckIfRoyaltyDetailsRecordExists(str_Company, d_agent_code, Imprint_PublisherName, Parent_PublisherName);
if (count == 0)
{
if (!sqlfunction.AddRoyaltyRecord(str_Company, Parent_PublisherName, Imprint_Publisher_AccountNo, Imprint_PublisherName, d_agent_code, d_royalty_percent, System.Security.Principal.WindowsIdentity.GetCurrent().Name))
{
failure_rows += ", " + (i + 2);
}
}
else
{
failure_existsrows += ", " + (i + 2);
}
}
}
}
else
{
lbl_UploadMessage.Text = "The excel file is missing a column. Please check the column headers.";
}
lbl_UploadMessage.Text = "Batch Upload process Completed.";
if (failure_rows != "Unable to Upload Records (Rows No): ")
{
lbl_failurerows.Text = failure_rows;
}
if (failure_existsrows != "Records already Exists (Row No): ")
{
lbl_failure_rowsexists.Text = failure_existsrows;
}
}